[Previous] [Contents] [Next]
Sum Every Second, Third, or nth Row or Cell
Every now and then you might want to sum every second, third, fourth, etc., cell in a spreadsheet. Now you can, with the following paragraphs.
Excel has no standard function that will sum every nth cell or row. However, you can accomplish this in a number of different ways. All these approaches use the
ROW function and the
ROW function returns the row number of a single cell reference:
MOD function returns the remainder after
number is divided by
ROW function within the
MOD function (to supply the number argument), divide it by 2 (to sum every second cell), and check to see whether the result is 0 (zero). If it is, the cell is summed.
You can use these functions in numerous ways-some of them producing better results than others. For instance, an array formula to
SUM every second cell in the range $A$1:$A$100 could look like this:
Because this is an array formula, you must enter it by pressing Ctrl-Shift-Enter. Excel will add the curly brackets so that it looks like this:
You must let Excel add these brackets, as adding them yourself will cause the array formula to fail.
Although this will do the job, it is not good spreadsheet design to use this method. It is an unnecessary use of an array formula. To make matters worse, it has the volatile
ROW function nested within it, making the whole array formula volatile. This means the formula would constantly recalculate whenever you are working in the workbook. This is a bad way to go!
Here's another formula you can use, which is a slightly better choice:
You should, however, be aware that this formula will return
#VALUE! if any cells in the range contain text rather than numbers. This formula, although not a true array, also will slow down Excel if too many instances of it are used, or if those instances reference a large range.
Fortunately, there is a much better way that is not only more efficient, but also far more flexible. This requires using the
DSUM function. For this example, we used the range A1:A500 as the range for which we need to sum every nth cell.
Enter the word
Criteria in cell E1. In cell E2, enter this formula:
Select cell C2 and then select Data » Validation. Select List from the Allow: box, and in the Source: box, type:
1,2,3,4,5,6,7,8,9,10. Ensure that the In-Cell drop-down box is checked and click OK. In cell C1, enter
every..... In any cell after row 1, enter this formula:
In the cell directly above where you entered the
DSUM function, enter this:
="Summing Every " & $C$2 &
CHOOSE($C$2,"st","nd","rd","th","th","th","th","th","th","th") & " Cell"
Now all you need to do is choose the desired number from cell C2 and the
DSUM function will do the rest.
As you can see from the figure, you can use one
DSUM function to sum each cell at the interval you specify. The
DSUM function is a far more efficient formula than an array formula or the
SUMPRODUCT function. Although setup can take a little more time, it's really a case of a little pain for a lot of gain.
Figure. Possible end result with formatting
[Previous] [Contents] [Next]