Excel Sum Every Other Row: Formulas for Both Patterns

Formulas & functions · Updated November 18, 2026

If your values are in A2:A20 and you want to add A2, A4, A6, and the rest of that alternating pattern, use =SUMPRODUCT((MOD(ROW(A2:A20)-ROW(A2),2)=0),A2:A20). It sums every other cell starting with the first cell in the range; change the condition to 1 if you want to start with the second cell instead.

The formula and how it works

The formula combines three ideas. ROW(A2:A20) produces the worksheet row numbers for the selected cells. Subtracting ROW(A2) makes the first cell’s offset zero, the next cell’s offset one, and so on. MOD(...,2) returns the remainder after dividing each offset by two, so the results alternate between 0 and 1. The condition =0 selects the first, third, fifth, and subsequent odd-positioned cells in the range.

SUMPRODUCT uses that true-or-false selection alongside the values. The two arrays must cover the same cells, which is why both references use A2:A20. Numeric cells selected by the condition contribute to the total; unselected cells do not. This method works in Microsoft 365 Excel for Windows and does not require a special array-formula keystroke.

The pattern is based on position within the range, not whether a worksheet row number is odd or even. That distinction matters: if the range starts at row 2, the first selected row is worksheet row 2, even though the formula is selecting the first position in the range.

Choose which row starts the pattern

Decide whether the first or second cell in your chosen range should be included. For a range beginning in A2, the first pattern means A2, A4, A6, and so on. To begin at A3 instead, retain the same range and change the remainder test from zero to one.

With values in A2:A20 and a pattern that starts with the second cell, use =SUMPRODUCT((MOD(ROW(A2:A20)-ROW(A2),2)=1),A2:A20). This selects A3, A5, A7, and the other cells in alternating positions. In general, the anchor inside ROW(A2) should be the first cell of the range, and the range in both parts of the formula should match.

If you specifically mean even-numbered worksheet rows, rather than every other position from a chosen starting cell, use the worksheet row numbers as the test. With values in A2:A20 and a goal of summing only even-numbered worksheet rows, the formula is =SUMPRODUCT((MOD(ROW(A2:A20),2)=0),A2:A20). This is different from the relative pattern when the range begins on an even row: the two may happen to produce the same result, but changing the range’s starting row can make them diverge.

Worked example with a small list

Suppose A2:A11 contains, in order, 10, 99, 20, 88, 30, 77, 40, 66, 50, and 55. If you want the first value and then every second value after it, the selected cells are A2, A4, A6, A8, and A10. Their values are 10, 20, 30, 40, and 50, so the correct total is 150.

With those example values in A2:A11 and the selection starting at A2, use =SUMPRODUCT((MOD(ROW(A2:A11)-ROW(A2),2)=0),A2:A11). The result is 150. To check the opposite pattern, the selected values would be 99, 88, 77, 66, and 55, which total 385. Testing both patterns against a short list is a useful way to catch an off-by-one choice before applying a formula to a large dataset.

You can also verify the selected positions manually by writing the row references down a few at a time. This is particularly useful when the source list starts below a heading, includes a subtotal row, or has an unusual starting position. The formula does not know which cells represent the records you intend to include; it follows the range and alternating pattern you specify.

Other ways to sum alternating rows

For a short list that will not change, you can name the cells directly in SUM. With the example values in A2:A11 and the desired values in A2, A4, A6, A8, and A10, use =SUM(A2,A4,A6,A8,A10). It returns 150. This is easy to inspect, but you must edit the formula yourself if rows are added or the list grows.

A helper column is useful when you want to see which records are included. With source values in A2:A11, enter =MOD(ROW()-ROW($A$2),2)=0 in B2 and fill it down through B11. The helper displays TRUE for rows that match the pattern and FALSE for the others. With values in A2:A11 and those TRUE/FALSE results in B2:B11, use =SUMIF(B2:B11,TRUE,A2:A11) to total the selected values; in the example, the result is 150.

The helper-column approach makes the selection visible and is convenient when other people need to review the rule. It also lets you filter or inspect the TRUE rows. The direct SUMPRODUCT formula is more compact when you only need the total and do not need to display the selection.

Blank cells, text, and hidden rows

Blank cells in the selected range do not add anything to the total. If some selected cells contain words such as a note or a label, keeping the condition and values as separate SUMPRODUCT arguments helps avoid treating that text as a value to multiply. Check for numbers stored as text, however: those may not behave like numeric entries in a calculation, even if they look like numbers on the sheet. Convert them to real numbers if they should count.

The formula follows worksheet row positions. A blank row still occupies a position in the alternating sequence, so it does not cause the formula to skip ahead to the next populated record. Similarly, hidden rows and rows excluded by a filter remain part of the calculation: ordinary SUMPRODUCT does not restrict the total to visible cells.

This is a common source of surprising totals. If the goal is every other physical row, including the effect of blank rows, the formula is doing the expected job. If the goal is every other nonblank record, or every other currently visible record after filtering, first define that different rule. A helper column that assigns a sequence to the records you care about is often easier to audit than treating physical row numbers as record numbers.

Common mistakes and checks

  • Starting on the wrong cell: The first cell in the range is selected when the test is =0. Use =1 to start with the second cell.
  • Using different ranges: The row test and the values being summed must refer to matching ranges of the same length.
  • Confusing worksheet rows with range positions: A relative pattern begins at the first cell you name. It does not automatically mean odd-numbered or even-numbered worksheet rows.
  • Including a heading: Start the range at the first data cell, not at a text heading, unless that is intentionally part of the pattern.
  • Expecting filtered rows to be ignored: Hidden and filtered-out rows are still included. Use a method designed around visible records if that is the requirement.
  • Forgetting that inserted rows affect the pattern: The formula follows cell positions. Inserting a row can change which records fall into the alternating sequence, so recheck the result after restructuring the sheet.

For a quick audit, temporarily list or highlight the cells that should count and add those values by hand. If the manual total and the formula disagree, check the first selected cell, range endpoints, blank rows, and any values stored as text. A mismatch is often a selection issue rather than an arithmetic error.

When to use a helper column instead

Use the direct formula when the pattern is strictly every other physical row and the only result you need is a sum. Use a helper column when the rule needs to be visible, when the data changes frequently, or when the records are not consistently arranged one per row. A helper can also make it clearer that the intended sequence is based on records rather than on their current worksheet positions.

For example, a report might place a detail row directly above each subtotal row. Summing every other row can work while that layout remains perfectly regular, but it can silently select the wrong values as soon as someone inserts a note or an extra detail line. In that situation, a category column or an explicit include flag is safer: the formula can sum rows identified as detail records rather than assuming the layout never changes.

Also consider whether the workbook is shared with people using older Excel versions. The SUMPRODUCT pattern here uses longstanding functions and does not depend on newer functions such as FILTER or SEQUENCE. In Excel for the web, the same formula can be entered in a cell; Mac users can use the same formula syntax, though keyboard shortcuts for editing or filling cells may differ.

Frequently Asked Questions

How do I sum every other row starting with row 1?
Set the range to start at the first data cell in row 1, such as A1, and use the relative-position formula with the condition =0. It will select the first, third, fifth, and subsequent positions in that range.

How do I sum every other row starting with the second row in my range?
Keep the range anchored at its first data cell and change the remainder condition from =0 to =1. That selects positions two, four, six, and so on.

Does the formula count hidden or filtered-out rows?
Yes. The ordinary SUMPRODUCT formula includes matching cells even when their rows are hidden or excluded by a filter. It follows row positions, not visibility.

Can I sum every other column instead?
Yes. Use the same principle with column positions: test the remainder of the column number relative to the first column in your range, then sum the matching cells. Keep the selection and value ranges the same size and shape.

Why does my total change after I insert a row?
The pattern is based on positions in the range. Inserting a row can shift which records occupy the selected positions. Recheck the range and the intended first included record after changing the sheet layout.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.