Sometimes you need to add only every 3rd row — a value that repeats in a pattern, totals scattered at a fixed interval. SUMPRODUCT with MOD builds a TRUE/FALSE mask of the rows you want and sums just those.
MOD(…, 3)=0 is TRUE on rows 0, 3, 6… from the start; SUMPRODUCT multiplies the mask by the values and adds them.
The example
Add every 3rd row (rows 2, 5, 8…).
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 1 | $10 |
| 3 | 2 | $20 |
| 4 | 3 | $30 |
| 5 | 4 | $40 |
| 6 | 5 | $50 |
| 7 | Sum of every 3rd: | $50 |
The formula
The pattern sum (rows 2 and 5: $10 + $40):
How it works
The MOD expression builds a mask of which rows to keep:
ROW(B2:B6)-ROW(B2)numbers the rows from 0: {0,1,2,3,4}.MOD(…, 3)=0is TRUE wherever that count divides evenly by 3 — positions 0 and 3 → {TRUE,FALSE,FALSE,TRUE,FALSE}.- Multiplying the mask by
B2:B6zeroes out the unwanted rows, leaving {10,0,0,40,0}. SUMPRODUCTadds them —50. Change the 3 to sum every 2nd, 4th, etc.
Want a different starting offset? Change which row you subtract. Subtracting ROW(B2)-1 shifts the pattern so it picks rows 1, 4, 7… instead.
Try it: interactive demo
Choose the interval; see which rows are summed.
row(s)Variations
Sum every other row (even/odd)
Interval of 2 picks alternating rows:
Sum every Nth column instead
Swap ROW for COLUMN:
Average every Nth row
Divide by the count of kept rows:
Pitfalls & errors
Forgetting -ROW(B2). Using raw ROW() ties the pattern to absolute sheet rows, so inserting rows above breaks it. Always subtract the first row to count from zero.
Off-by-one on the offset. =0 starts the pattern on the first row; use =1 or =2 to shift which rows are picked.
Text in the value range can cause #VALUE! inside SUMPRODUCT. Keep the summed range numeric, or wrap values in N().
Practice workbook
Frequently asked questions
How do I sum every Nth row in Excel?
How do I sum every other row?
Why does my every-Nth-row sum break when I insert rows?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class