# How to use Sum Every nth Row in Excel

Nov 06, 2020 • edited Nov 07, 2020 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. 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. 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. 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 a range. The array constant looks as

follows.

{0;0;5;0;0;66;0;0;21}

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