How to sum every nth row
✓ Verified in LibreOffice 25.8.7.3Add up every 2nd, 3rd, or nth value in a column — common with repeating blocks of data.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT((MOD(ROW(B2:B7)-ROW(B2),3)=0)*B2:B7) | Change the 3 to your n. Anchoring on ROW(B2) makes the first row count as position 0. |
| Google Sheets | =SUMPRODUCT((MOD(ROW(B2:B7)-ROW(B2),3)=0)*B2:B7) | Identical. |
| LibreOffice Calc | =SUMPRODUCT((MOD(ROW(B2:B7)-ROW(B2),3)=0)*B2:B7) | Identical. |
How it works
ROW(B2:B7)-ROW(B2) numbers the rows 0,1,2,3,4,5 regardless of where the range starts; MOD(...,3)=0 is true for positions 0 and 3 — every 3rd row. SUMPRODUCT multiplies those TRUE/FALSE values (1/0) by the numbers and sums: 10 + 40 = 50. Every-other-row is the same formula with 2, and MOD(...,3)=1 shifts the starting offset.
Verified, not just documented
We ran =SUMPRODUCT((MOD(ROW(B2:B7)-ROW(B2),3)=0)*B2:B7) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 50 — exactly the expected result. Every formula here is confirmed by actually executing it.