Table of Contents
You can use the following basic formula to sum every nth row in Excel:
=SUM(A1:A20*(MOD(ROW(A1:A20),4)=0))
This particular formula calculates the sum of every 4th row in the range A1:A20.
Simply change the value in the ROW() function to sum a different interval of rows.
For example, you can use the following formula to sum every 3rd row:
=SUM(A1:A20*(MOD(ROW(A1:A20),3)=0))
The following example shows how to use this formula in practice.
Example: Sum Every Nth Row in Excel
Suppose we have the following list of values in Excel:
We can use the following formula to sum every fourth row in the range:
=SUM(A1:A20*(MOD(ROW(A1:A20),4)=0))
The following screenshot shows how to use this formula in practice:
We can see that the sum of the values in every 4th row is 66.
We can verify this is correct by manually calculating the sum of every 4th row:
If you’d like to sum the value of every nth row starting with the first row, you can subtract 1 within the ROW() function of the formula:
=SUM(A1:A20*(MOD(ROW(A1:A20)-1,4)=0))
The following screenshot shows how to use this formula in practice:
We can verify that this formula calculates the sum of every 4th row starting with the first row:
Sum of Every 4th Row: 8 + 5 + 14 + 12 + 29 = 68.
This matches the value calculated by the formula.