Sum Every nth Row


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 Result

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 formula.

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


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