This post demonstrates two formulas to sum every nth row in a list. The list is in A1:A9. The first formula sums every nth row starting at the nth row. If you’re summing every third row, it starts at the third row. The formula in B1 is =MOD(ROW(),B$11) and is copied down and accross to show how the MOD function is used in the formulas.

The formula for row 17 starts at a specific row (row 3 in this example). It’s a monster of a formula, so let’s break it down.

=SUM((MOD(OFFSET($A$1,B$15-1,0,MAX(ROW($A$1:$A$9))-MIN(ROW($A$1:$A$9))+1-B$15+1,1),B$11)
=MOD(OFFSET($A$1,B$15-1,0),B$11))*
(OFFSET($A$1,B$15-1,0,MAX(ROW($A$1:$A$9))-MIN(ROW($A$1:$A$9))+1-B$15+1,1)))

To simplify, the formula can be viewed like this:

=SUM((MOD(Range,B11)=MOD(Start_Cell,B11))*(Range))

That’s pretty much the same as the first formula, except that the MOD of each cell is compared to the MOD of the starting cell instead of zero. Now you just have to insert the Range and Start_Cell formulas in there. The Range formula looks like this

OFFSET($A$1,B$15-1,0,MAX(ROW($A$1:$A$9))-MIN(ROW($A$1:$A$9))+1-B$15+1,1)

Start at A1, the first cell in our range. Move down 2 rows (B15-1) to get to A3. Move over zero columns, still at A3. The number of rows is the total rows (9 = MAX – MIN +1) less 3 (B15) plus 1. The number of columns is 1. With B15 equal to 3, the whole formula evaluates to A3:A9. This same formula is used in the second half of the formula, because in array formulas the row count has to be the same.

The Start_Cell formula is this

OFFSET($A$1,B$15-1,0),B$11)

This is like an abbreviated version of the Range formula. For this, we just need one cell, so the rows and columns arguments are omitted and just the first three arguments are included.