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 MOD function.
The ROW function returns the row number of a single cell reference:
ROW(reference)
The MOD function returns the remainder after number is divided by divisor:
MOD(number,divisor)
Nest the 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:
=SUM(IF(MOD(ROW($A$1:$A$500),2)=0,$A$1:$A$500,0))
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:
{=SUM(IF(MOD(ROW($A$1:$A$500),2)=0,$A$1:$A$500,0))}
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:
=SUMPRODUCT((MOD(ROW($A$1:$A$500),2)=0)*($A$1:$A$500))
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:
=MOD(ROW(A2)-$C$2-1,$C$2)=0
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 SUM every..... In any cell after row 1, enter this formula:
=DSUM($A:$A,1,$E$1:$E$2)
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
