# How to sum every nth row in Excel

Test each row's position with MOD and add the rows where it divides evenly: `=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11)`.

**Excel:** `=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Line | Amount |  | Every Nth row | 2 |
| Jan budget | 12000 |  | Total |  |
| Jan actual | 11640 |  |  |  |
| Feb budget | 12000 |  |  |  |
| Feb actual | 12385 |  |  |  |
| Mar budget | 12500 |  |  |  |
| Mar actual | 12210 |  |  |  |
| Apr budget | 12500 |  |  |  |
| Apr actual | 13045 |  |  |  |
| May budget | 13000 |  |  |  |
| May actual | 12870 |  |  |  |

Formula in E2: `=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11)`

Result: $62,150

## How it works

`ROW(B2:B11)-ROW(B2)+1` numbers the rows 1 to 10 inside the range. MOD with 2 is 0 for positions 2, 4, 6, 8 and 10, which are the actual lines. SUMPRODUCT multiplies those TRUE results by the amounts and adds them. Change E1 to 3 to add every third row.

## Other ways

- `=SUM(FILTER(B2:B11,MOD(SEQUENCE(ROWS(B2:B11)),E1)=0))`: Excel 365 and Google Sheets: SEQUENCE numbers the rows and FILTER keeps every Nth one.
- `=SUMIF(A2:A11,"*actual",B2:B11)`: When the rows are labeled, matching the label is clearer and survives inserted rows.

## Common mistakes

- Using ROW() alone counts from the top of the sheet, so the pattern shifts if the data does not start in row 1. Subtract ROW of the first cell as shown.
- Inserting a row in the middle shifts every row after it. If the rows have labels, sum by label instead.

## Questions

**How do I sum every other row starting with the first?**

Count the positions from 0 instead of 1: `=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2),2)=0)*B2:B11)` adds rows 1, 3, 5 and so on of the range.

**How do I sum every nth column?**

Use COLUMN in place of ROW: `=SUMPRODUCT((MOD(COLUMN(B2:M2)-COLUMN(B2)+1,3)=0)*B2:M2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-every-nth-row