How to use Sum Every nth Row in Excel

Nov 06, 2020 • edited Nov 07, 2020

How to use Sum Every nth Row in Excel

This example shows you how to create an array formula that sums every nth row in Excel. We will show it for n = 3, but you can do this for any number.

1. The ROW function returns the row number of a cell.

Row Function in Excel

2. The MOD function gives the remainder of a division. For example, for the first row, MOD(1,3) equals 1. 1 is divided by 3 (0 times) to give a remainder of 1. For the third row, MOD(3,3) equals 0. 3 is divided by 3 (exactly 1 time) to give a remainder of 0. As a result, the formula returns 0 for every 3th row.

Mod Function

Note: change the 3 to 4 to sum every 4th row, to 5 to sum every 5th row, etc.

3. Slightly change the formula as shown below.


4. To get the sum of the product of these two ranges (FALSE=0, TRUE=1), use the SUM function and finish by pressing CTRL + SHIFT + ENTER.

Sum Every nth Row in Excel

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the


Explanation: The product of these two ranges (array constant) is stored in Excel's memory, not in a range. The array constant looks as



This array constant is used as an argument for the SUM function, giving a result of 92.

#Tutorial#How To#Functions#Array Formulas

How to use Substitute vs Replace in Excel

How to use Sum Largest Numbers in Excel