How to Sum Every Nth Row in Excel

Total every 3rd row of a single-column report with SUMPRODUCT, MOD and ROW — and pick any starting position in the pattern.

You will learn to

• Sum every 3rd row starting from the first
• Shift the pattern to a different position
• Turn an every-nth total into a share

How it works

Some exports stack several values per record in one column — cash, card and online takings for each day, or regular, overtime and total hours per employee. To add every nth row, number the rows from 0 with ROW(B2:B16)-ROW(B2), take the remainder after dividing by n with MOD, and keep the rows where the remainder matches: =SUMPRODUCT((MOD(ROW(B2:B16)-ROW(B2),3)=0)*B2:B16).

A remainder of 0 picks the 1st, 4th, 7th… rows; 1 picks the 2nd, 5th, 8th…; and so on. Change 3 to 2 to sum every other row.

=SUMPRODUCT((MOD(ROW(B2:B16)-ROW(B2),3)=0)*B2:B16)