How to Sum Every Other Row or Every Nth Row in Excel

To sum every other row in Excel, you need a way to tell even rows from odd rows. MOD and ROW do that, and SUMPRODUCT adds up the rows that pass: =SUMPRODUCT((MOD(ROW(C2:C9),2)=0)*C2:C9). This guide explains how the formula works, how to change it to every 3rd or Nth row, and when a simpler SUMIF does the job better.

The example: a budget with alternating rows#

Many budgets and reports alternate between two kinds of row. Here, each item has a Budget row followed by an Actual row, in A1:C9:

A plain =SUM(C2:C9) gives 3,625, which mixes both. You want the Budget total (even rows 2, 4, 6, 8) and the Actual total (odd rows 3, 5, 7, 9) separately.

How MOD and ROW find alternate rows#

ROW returns a cell's row number, and MOD returns the remainder after division. Dividing a row number by 2 leaves 0 for even rows and 1 for odd rows.

So MOD(ROW(C2:C9),2)=0 produces a list of TRUE and FALSE values: TRUE for rows 2, 4, 6 and 8. That list is the filter. The guide to MOD and INT explains remainders in more detail.

How to sum every other row with SUMPRODUCT#

Multiply the TRUE/FALSE test by the amounts and let SUMPRODUCT add the results.

=SUMPRODUCT((MOD(ROW(C2:C9),2)=0)*C2:C9)

When Excel multiplies, TRUE counts as 1 and FALSE as 0. So even rows keep their amount and odd rows become 0. The result is 1,810 (1,200 + 400 + 150 + 60), the Budget total.

For the odd rows, change =0 to =1:

=SUMPRODUCT((MOD(ROW(C2:C9),2)=1)*C2:C9)

That returns 1,815 (1,200 + 452 + 98 + 65), the Actual total. SUMPRODUCT handles the whole list of values without Ctrl+Shift+Enter, in every version of Excel. The SUMPRODUCT function guide and the article on SUMPRODUCT explain how it works with arrays.

You can count the matching rows the same way, without the amounts: =SUMPRODUCT(--(MOD(ROW(C2:C9),2)=0)) returns 4, the number of Budget rows. Divide the total by that count and you have the average Budget amount, 452.50.

Note that the test uses the real row number on the sheet, not the position in your range. If your data starts on an odd row, "even" might be the second kind of row, not the first. Check one row by hand before you trust the total.

How to sum every 3rd or every Nth row#

Subtract the first row number so the count starts at 0, then change the 2 to N.

=SUMPRODUCT((MOD(ROW(C2:C13)-ROW(C2),3)=0)*C2:C13)

ROW(C2:C13)-ROW(C2) turns the rows into 0, 1, 2, 3 and so on. MOD by 3 is 0 for positions 0, 3, 6 and 9, so the formula adds C2, C5, C8 and C11: the first cell and every third cell after it.

To start from the second cell instead, test for =1. To sum every 4th row, change 3 to 4. Because the formula subtracts ROW(C2), it keeps working if you insert rows above the table.

The same idea works across columns. Swap ROW for COLUMN: =SUMPRODUCT((MOD(COLUMN(B2:I2),2)=0)*B2:I2) adds the even-numbered columns in row 2.

Using FILTER in Microsoft 365#

In Excel 2021 or Microsoft 365, FILTER can pick out the rows, and SUM adds them.

=SUM(FILTER(C2:C9,MOD(ROW(C2:C9),2)=0))

This also returns 1,810. Many people find it easier to read: "sum the values, filtered to even rows". FILTER needs a dynamic-array version of Excel, so use SUMPRODUCT if the file will be opened in Excel 2019 or older. For more on this pattern, see how to filter and sum.

When SUMIF is the better choice#

If the rows carry a label, sum by the label instead of by position. In the budget, column B says Budget or Actual, so:

=SUMIF(B2:B9,"Actual",C2:C9)

This returns 1,815, the same as the odd-row formula, and it's clearer for anyone reading the sheet. It also keeps working if someone sorts the table or inserts a row in the wrong place, which would break a position-based formula. Use the MOD and ROW method only when there's no label to match. Data exported from accounting or survey tools is a common case: the pattern is fixed, but the rows carry no type column to filter on.

Common mistakes and how to fix them#

Watch for these problems:

1. #VALUE! from text. If the range contains text, such as a "n/a", multiplying it fails. Use the comma form, which treats text as 0: =SUMPRODUCT(--(MOD(ROW(C2:C9),2)=0),C2:C9). The -- turns TRUE and FALSE into 1 and 0.
2. Even and odd swapped. The test uses sheet row numbers. If the total looks like the wrong half, switch =0 and =1.
3. Including the header. Start the range on the first data row, not the header row.
4. Hidden rows. These formulas include hidden and filtered rows. To add only what you can see, use SUBTOTAL; see how to sum only visible cells and the comparison of SUBTOTAL vs SUM.

For a refresher on totals in general, read the guide to the SUM function. If the alternating rows are on different sheets instead, see how to sum across sheets.

Practise it#

Every-other-row formulas build on one skill: knowing exactly which cells SUM is adding. Practise SUM and AutoSum in a live spreadsheet: you type the formula and it checks it instantly. Then try the exercise on this page to add only the Actual rows with SUMPRODUCT.