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.





Formula to Sum Every nth Row

The formula below is entered in cell E1.



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: The MOD Function used in Excel VBA to check if a number is even.

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

{0;0;0;0;1;0;0;0;0;1;0;0;0;0;1;0;0;0;0;1}

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: See the SUMPRODUCT function used to count values based on multiple conditions.

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 – Microsoft Excel Tips and Tricks