How to sum every nth row in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11)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).
- Result on the example
- $62,150 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11)Result62150
B2:B1110 rows × 1 col: $12,000, $11,640, $12,000, $12,385, …A range: 10 cells in one column.
ROW(B2:B11){10 rows × 1 col} 2, 3, 4, 5, …ROW: Row number of a reference, or of this cell.- reference
B2:B1110 rows × 1 col: $12,000, $11,640, $12,000, $12,385, …
- reference
B2$12,000The value in B2.
ROW(B2)2ROW: Row number of a reference, or of this cell.- reference
B2$12,000
- reference
ROW(B2:B11)-ROW(B2){10 rows × 1 col} 0, 1, 2, 3, …The - operator subtracts.
ROW(B2:B11)-ROW(B2)+1{10 rows × 1 col} 1, 2, 3, 4, …The + operator adds.
E12The value in E1.
MOD(ROW(B2:B11)-ROW(B2)+1,E1){10 rows × 1 col} 1, 0, 1, 0, …MOD: Remainder after division.- number
ROW(B2:B11)-ROW(B2)+1{10 rows × 1 col} 1, 2, 3, 4, … - divisor
E12
- number
MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0{10 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11{10 rows × 1 col} 0, 11640, 0, 12385, …The * operator multiplies.
SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11)62150SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11{10 rows × 1 col} 0, 11640, 0, 12385, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11). We ran it in Google Sheets mode on the same data and it returns $62,150.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(FILTER(B2:B11,MOD(SEQUENCE(ROWS(B2:B11)),E1)=0)) | $62,150 | Excel 365 and Google Sheets: SEQUENCE numbers the rows and FILTER keeps every Nth one. |
| =SUMIF(A2:A11,"*actual",B2:B11) | $62,150 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum every nth row
E2, add up every E1-th row of B2:B11, starting with the E1-th row (here, the actuals).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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.