ThisCalculator

Everyday

Sum Every Nth Row in Excel: 4 Easy Ways

Updated · Standard formula, shown below

Sum Every Nth Row in Excel: 4 Easy Ways

This guide explains how to sum every nth row in Excel, such as every 2nd, 3rd, or 5th row, using formulas. You will learn the SUMPRODUCT, SUM, and FILTER methods, how to handle blank cells, and how to choose the right approach for your data.

Why Sum Every Nth Row?

Summing every nth row is a common need when your data is structured with repeating blocks. For example, you might have a spreadsheet where every 3rd row is a subtotal, or you might want to extract a weekly total from daily data. Instead of manually selecting rows, you can use a formula that automatically sums every 2nd, 3rd, 4th, or any other interval.

The Core Formula: SUMPRODUCT with MOD

The most flexible way to sum every nth row uses the SUMPRODUCT function combined with the MOD (modulus) function. The MOD function returns the remainder after division, which lets you identify rows that fall at a specific interval.

The general formula is:

=SUMPRODUCT((MOD(ROW(range)-ROW(first_cell)+1, n)=0)*range)

Here’s what each part does:

  • ROW(range) returns an array of row numbers for the range.
  • ROW(first_cell) subtracts the first row number so your count starts at 1, even if your data starts on row 5.
  • +1 makes the first row count as 1, not 0.
  • MOD(…, n) returns 0 for rows that are multiples of n.
  • SUMPRODUCT multiplies this 0/1 array by your values and adds the results.

Worked Example: Sum Every 3rd Row

Suppose your data is in cells A2:A20, and you want to sum every 3rd row starting from row 2 (i.e., rows 2, 5, 8, 11, 14, 17, 20). The formula is:

=SUMPRODUCT((MOD(ROW(A2:A20)-ROW(A2)+1,3)=0)*A2:A20)

Let’s break it down with a small example. If A2:A10 contains the numbers 10, 20, 30, 40, 50, 60, 70, 80, 90, then:

  • ROW(A2:A10) returns {2;3;4;5;6;7;8;9;10}
  • ROW(A2) is 2, so subtract 2 and add 1: {1;2;3;4;5;6;7;8;9}
  • MOD({1;2;3;4;5;6;7;8;9},3) returns {1;2;0;1;2;0;1;2;0}
  • Comparing to 0 gives {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
  • Multiplying by A2:A10 gives {0;0;30;0;0;60;0;0;90}
  • SUMPRODUCT sums these to 180.

So the result is 180, which is 30+60+90.

Alternative: SUM with FILTER (Excel 365)

If you have Excel 365 or Excel 2021, you can use the FILTER function, which is more readable:

=SUM(FILTER(A2:A20, MOD(ROW(A2:A20)-ROW(A2)+1,3)=0))

This does the same thing but uses FILTER to extract the rows first, then SUM adds them. It’s easier to understand at a glance, but it won’t work in older Excel versions.

How to Change the Interval (n)

To sum every 2nd row, change the 3 to 2. To sum every 4th row, change to 4. You can also put the interval in a cell (e.g., D1) and reference it:

=SUMPRODUCT((MOD(ROW(A2:A20)-ROW(A2)+1,$D$1)=0)*A2:A20)

This way you can change the interval without editing the formula.

Handling Blank Cells and Errors

If your range contains blank cells, the formula treats them as 0, which is fine. However, if your range contains text or errors, you’ll get an error. To ignore text, you can multiply by the ISNUMBER function:

=SUMPRODUCT((MOD(ROW(A2:A20)-ROW(A2)+1,3)=0)*ISNUMBER(A2:A20)*A2:A20)

This only sums numeric cells that are in the nth row.

Common Mistakes and How to Avoid Them

  • Off-by-one errors: The +1 in the formula is crucial. Without it, the first row of your range is considered row 0, so MOD(0,n)=0, and you’d sum the first row, which might not be what you want.
  • Using ROW() without a range: ROW() alone returns the current row, not an array. Always use a range like ROW(A2:A20).
  • Forgetting to press Ctrl+Shift+Enter: In older Excel versions, array formulas need to be entered with Ctrl+Shift+Enter. In Excel 365, SUMPRODUCT and FILTER handle arrays natively, so you don’t need to.
  • Using SUM instead of SUMPRODUCT: SUM won’t work with array operations unless you enter it as an array formula. SUMPRODUCT is designed for this.

Frequently Asked Questions

How do I sum every nth row starting from row 1?

If your data starts at row 1, the formula becomes =SUMPRODUCT((MOD(ROW(A1:A20),n)=0)*A1:A20). Since ROW(A1) is 1, you don’t need the +1 adjustment. For example, to sum every 2nd row from row 1, use =SUMPRODUCT((MOD(ROW(A1:A20),2)=0)*A1:A20).

Can I sum every nth column instead?

Yes, replace ROW with COLUMN. For example, to sum every 2nd column in row 1: =SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0)*A1:J1). The logic is identical.

What if I want to sum every nth row but skip the header?

Just start your range below the header. For example, if row 1 is a header, use A2:A100. The formula will automatically count from row 2 as the first data row.

Why does my formula return 0?

Check if your range is correct and that the interval n is a positive integer. Also, make sure your data is numeric. If you have text in the range, the multiplication will fail and return 0. Use the ISNUMBER version to ignore text.

Is there a way to do this without a formula?

You can use the Subtotal feature in Excel, but it requires sorting or filtering. A more manual method is to insert a helper column with a formula like =MOD(ROW(),n) and then use SUMIF on that column. But the SUMPRODUCT method is the most direct.

Does this work in Google Sheets?

Yes, the SUMPRODUCT and MOD functions work the same way in Google Sheets. The FILTER method also works in Google Sheets.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.