Sum every other row (or every nth row) in Excel

Every other row with SUMPRODUCT and MOD

Exported reports often stack two lines per item. Here each store has a Units row and then a Revenue row, so the revenue figures are every second value in C2:C9. A plain SUM(C2:C9) mixes units and dollars and gives a meaningless 65,250.

SEQUENCE(ROWS(C2:C9)) numbers the rows of the range 1 to 8. MOD(...,2) gives the remainder after dividing by 2: 1 for odd positions, 0 for even ones. Testing =0 keeps positions 2, 4, 6 and 8, which are the Revenue rows. SUMPRODUCT multiplies each TRUE (1) or FALSE (0) by the value next to it and adds the results.

Every 3rd row (or every nth row)

The call log in E1:F10 has three shifts per day: early, late and night. To total just the night shifts, take every third value. The only change is the divisor in MOD: 3 instead of 2.

With MOD(position,3), positions 3, 6 and 9 give a remainder of 0. Test =1 for positions 1, 4, 7 (the early shifts) or =2 for 2, 5, 8 (the late shifts). For every 4th or 5th row, put that number in place of the 3. A cleaner version keeps n in a cell, say H2, and uses MOD(...,H2)=0, so you can change it without editing the formula.

The safer layout: sum by label with SUMIF

Position-based formulas break quietly. If someone inserts a Margin row for one store, every row below it shifts by one and the "every other row" total starts adding units to revenue. Nothing turns red; the number is just wrong.

When the data has a label column, use it. SUMIF picks rows by what they are, not where they sit, so inserted, deleted or re-sorted rows cannot throw it off. This is the method to pick for anything that gets edited or refreshed. If there is no label column, adding one is usually a better fix than a clever formula.

Labels that are only part of the text: wildcards

The shift labels combine the day and the shift ("Mon night"), so there is no column that says just "night". A wildcard handles it: "*night" means "anything, then the word night at the end". Use "*night*" if the word could appear anywhere in the label.

To add a second condition, switch to SUMIFS, which puts the sum range first: =SUMIFS(C2:C9,B2:B9,"Revenue",A2:A9,"<>Bath") gives 49,385, revenue for every store except Bath.

The header-row trap

If you select C1:C9 instead of C2:C9, two things go wrong. With the * form, SUMPRODUCT multiplies the text "Q3" by a number and returns #VALUE!. With the comma form below, text counts as 0 so there is no error, but the header now sits at position 1, which shifts everything: position 2 is Leeds Units, not Leeds Revenue.

So the comma form silently sums the Units rows. Always start the range on the first data row, and check the answer once by hand on a small block.

Older Excel and other options

SEQUENCE needs Excel 365 or Excel 2021 and later. In older versions, use ROW to build the positions: =SUMPRODUCT((MOD(ROW(C2:C9)-ROW(C2),2)=1)*C2:C9) gives the same revenue total. Subtracting ROW(C2) makes the count start at 0 on the first row of the range, so the formula still works if the data moves down the sheet.

In Excel 365 you can also write =SUM(FILTER(C2:C9,MOD(SEQUENCE(ROWS(C2:C9)),2)=0)), which reads well but returns #CALC! if no row matches. A third option needs no formula skills: add a helper column, mark each row you want, and SUMIF on that column.

1. Insert a column next to the data and type a header such as Keep.
2. In the first two data rows type 0 and 1 (or 0, 0, 1 for every 3rd row).
3. Select those cells and double-click the fill handle to repeat the pattern down the column.
4. Total the marked rows with =SUMIF(helper_range,1,C2:C9).