How to Sum Every Nth Row in Excel

To sum every Nth row in Excel, you can use a combination of the SUM and MOD functions in an array formula. Here's a step-by-step guide on how to do this:

1. Identify the column with the numbers you want to sum.
2. Determine the value of N, which is the number of rows between the rows you want to sum.
3. Click on an empty cell where you want to display the sum of every Nth row.

Now, follow these steps to create the array formula:

1. Type =SUM(
2. Press Ctrl + Shift + Enter to start an array formula. You'll see { and } appear around the formula.
3. Type IF(MOD(ROW( to start the conditional statement.
4. Select the range of cells in the column with the numbers you want to sum. For example, if you want to sum cells A1 to A20, click and drag from A1 to A20.
5. Type )-ROW( and click on the first cell in the selected range, in this case, A1.
6. Type ), and enter the value of N, in this case, 2.
7. Type )=0, to close the condition.
8. Select the range of cells in the column again (A1:A20).
9. Press Ctrl + Shift + Enter to close the array formula. You'll see { and } appear around the formula, and the sum of every Nth row should be displayed in the selected cell.

Here's the final formula for this example:

{=SUM(IF(MOD(ROW(A1:A20)-ROW(A1),2)=0,A1:A20))}

This formula will sum every 2nd row in the range A1:A20.

Example

Here's an example to demonstrate how to sum every Nth row in Excel:

1. In column A, enter the following numbers in cells A1 to A10:

1
2
3
4
5
6
7
8
9
10

2. Let's say we want to sum every 3rd row in this range. N will be 3.
3. Click on an empty cell, e.g., B1.
4. Enter the following formula and press Ctrl + Shift + Enter:

{=SUM(IF(MOD(ROW(A1:A10)-ROW(A1),3)=0,A1:A10))}

5. The result in cell B1 should be 22 (1 + 4 + 7 + 10).