Summing Every Nth Row

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.

SumEvery

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.

One thought on “Summing Every Nth Row

  1. In attempting to replicate your example in Excel XP, I’m unable to generate the values in row 13, and my values in row 17 are ‘3’ all the way across. The formula bar in the picture shows a formula in cell B13, which I entered in my worksheet, but it doesn’t display a number. When I enter

    {=SUM((MOD($A$1:$A$9,B$11)=0)*($A$1:$A$9))}

    into cell B13, it displays as text. Would it be possible for someone to send me a working copy of this example, or to have a link on this page to download the sample file?

    Thanks,
    Tom

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *