Sum Every Nth Row in a List

0 Flares 0 Flares ×

At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.

The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.

Sales Totals distributed throughout a list

Formula to Sum Every nth Row

The formula below is entered in cell E1.

Sum Every Nth Row in a List

Let’s break the formula down a little.

ROW: The ROW function is used to return the row number that the formula is checking. -2 has been entered on the end because the values begin from row 3. This -2 ensures that instead of using rows 7, 12, 17 and 22. Rows 5, 10, 15 and 20 are used.

MOD: The MOD function used to find the remainder after a number is divided by a divisor. It is used in this formula to check whether it is the fifth row or not. If it is the fifth row, after dividing the row number by five, the result will be 0.

Note: .

SUMPRODUCT: The SUMPRODUCT function will perform the summing. The first array is the test for the row. This is what is returned;


The 1 is returned when the condition is true. This is multiplied by the range of values and then summed which gives us our total.

Note: .

Watch the Video

I hope this explanation makes sense and you are able to adapt it for your own situation. If not, please check out this video.

Sum Every Nth Row in a List –

One thought on “Sum Every Nth Row in a List

  1. thanks a lot for this useful explanation
    i want to know if there is way to calculate the average of every 10 cells in one column
    thanks in advance

Leave a Reply

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