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.
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
This is exactly what I needed to do, but I got the wrong answer. My total starts on the 10th row then I have to add every 13th row thereafter. It is 13 because there are 12 rows in between, one for each month of the year. The formula is inserted on the 9th row right above the first total on row 10. What did I do wrong?
=SUMPRODUCT((MOD(ROW($E$10:$E)-9,13)=0)*($E$10:$E))
This was a huge help. None of the other tutorials covered how to offset the starting row number. Thank you!!
You’re welcome. That is great to hear, Manuel.