How to Sum Every Alternate Row in Excel (Formula & Examples)

Learn how to sum every alternate row in Excel using SUMPRODUCT, MOD, ROW and SUMIF formulas with simple examples and step-by-step instructions.

 

How to Sum Every Alternate Row in Excel (Formula & Examples)

   Learn how to sum every alternate row in Excel with easy formulas using SUMPRODUCT, MOD, ROW and SUMIF. Includes practical examples to calculate alternate-row totals quickly and accurately.

How to Sum Every Alternate Row in Excel (Formula & Examples)

When working with large Excel spreadsheets, you may sometimes need to add values from every other row instead of calculating the total of an entire column. For example, you might want to sum sales recorded on alternate days, calculate expenses from specific rows, or add values from alternating entries in a report.

Doing this manually can be time-consuming, especially when your worksheet contains hundreds or thousands of rows. Fortunately, Excel provides a few simple formulas that can automatically calculate the total of every alternate row.

In this guide, you'll learn different ways to sum alternate rows in Excel, with practical examples and easy-to-follow formulas.

What Does Summing Every Alternate Row Mean in Excel?

Summing every alternate row means adding values from rows that follow a repeating pattern, usually skipping one row between each selected row.

For example, suppose you have sales figures in column B:

RowSales Amount
2500
3300
4700
5200
6600
7400
8800
9350

If you want to add every alternate row starting from B2, Excel should calculate:

500 + 700 + 600 + 800 = 2,600

Instead of selecting each cell individually, you can use a formula to automate the calculation.

Method 1: Sum Every Alternate Row Using the MOD Function

The MOD function is one of the easiest ways to sum alternate rows in Excel. It checks the remainder after dividing a number by another number.

By combining MOD with SUMPRODUCT and ROW, you can identify rows that match your required pattern.

Formula:

=SUMPRODUCT((MOD(ROW(B2:B9)-ROW(B2),2)=0)*B2:B9)

How this formula works:

  • ROW(B2:B9) returns the row numbers of the selected cells.

  • ROW(B2) identifies the starting row.

  • MOD(...,2) checks whether the row's relative position is even or odd.

  • =0 selects every second row, starting with B2.

  • SUMPRODUCT adds the selected values.

Using the example above, the formula returns 2,600.

When to use this formula: Choose this method when you want a flexible formula that starts from a specific cell and automatically adjusts to the selected range.

Method 2: Sum Alternate Rows Starting from the Second Row

Sometimes, you may want to skip the first row and start adding values from the second row in your range.

For example, if your data is in B2:B9 and you want to add B3, B5, B7 and B9, use this formula:

=SUMPRODUCT((MOD(ROW(B2:B9)-ROW(B2),2)=1)*B2:B9)

The formula selects the alternate positions beginning with the second cell in the range.

Example calculation:

300 + 200 + 400 + 350 = 1,250

This approach is useful when your spreadsheet contains alternating categories, such as income and expenses, or when you need to total values from a specific starting position.

Method 3: Sum Every Alternate Row Using SUMPRODUCT and MOD

You can also use a shorter formula when your data starts from a known row.

Suppose your numbers are stored in cells B2:B20, and you want to sum rows 2, 4, 6, 8 and so on.

Use:

=SUMPRODUCT((MOD(ROW(B2:B20),2)=0)*B2:B20)

This formula checks the actual Excel row numbers.

  • Even-numbered worksheet rows are included.

  • Odd-numbered worksheet rows are excluded.

Important: Unlike the previous method, this formula depends on the actual worksheet row numbers. If your data starts in row 3, the selection pattern will be different.

Method 4: Sum Alternate Rows Using SUM and Individual Cell References

For a small spreadsheet, you can use the SUM function with individual cell references.

For example:

=SUM(B2,B4,B6,B8,B10)

This formula adds the values from five alternate rows.

Although this method is simple, it is not ideal for large datasets. Whenever you insert new rows or extend your data, you may need to update the formula manually.

Use this approach when you only have a few cells to calculate and do not expect the data range to change frequently.

Method 5: Sum Every Alternate Row Using a Helper Column

If you prefer a formula that is easy to understand and maintain, a helper column can be a practical solution.

Let's assume your sales figures are in column B, starting from B2.

Follow these steps:

Step 1: Add a new column called "Include".

Step 2: Enter this formula in C2:

=MOD(ROW()-ROW($B$2),2)=0

Step 3: Drag the formula down alongside your sales data.

Excel will return TRUE for the first, third, fifth and other alternate positions, and FALSE for the remaining positions.

Step 4: Use the SUMIF function to calculate the total:

=SUMIF(C2:C9,TRUE,B2:B9)

The formula adds only the sales amounts where the helper column contains TRUE.

This method is particularly helpful when working with large reports because you can easily see which rows are included in the calculation.

Practical Example: Calculate Sales from Every Alternate Day

Imagine you manage a small business and record daily sales in Excel. You want to calculate sales for every alternate day, starting with Monday.

Your worksheet looks like this:

DaySales
Monday1,500
Tuesday2,000
Wednesday1,800
Thursday2,500
Friday2,200
Saturday3,000
Sunday2,800

Assuming the sales amounts are in B2:B8, enter:

=SUMPRODUCT((MOD(ROW(B2:B8)-ROW(B2),2)=0)*B2:B8)

Excel selects Monday, Wednesday, Friday and Sunday.

Total alternate-day sales:

1,500 + 1,800 + 2,200 + 2,800 = 8,300

You can use the same formula for monthly reports, expense tracking, inventory records and other financial calculations.

Comparison of Different Methods to Sum Alternate Rows

Here is a quick comparison to help you choose the right formula.

MethodFormula or FunctionBest For
MOD + SUMPRODUCTSUMPRODUCTFlexible alternate-row calculations
MOD with 1SUMPRODUCTStarting from the second position
ROW + MODSUMPRODUCTSelecting even or odd worksheet rows
Individual referencesSUMSmall datasets
Helper columnSUMIFLarge and easy-to-check datasets

Common Mistakes to Avoid

Even a simple Excel formula can return an unexpected result if the range or starting position is incorrect.

Here are a few things to check:

  • Incorrect starting cell: Make sure the formula starts from the correct row. Otherwise, Excel may select the opposite set of alternate rows.

  • Wrong range: Check that the range includes all the values you want to calculate.

  • Using actual row numbers: Remember that MOD(ROW(range),2) follows worksheet row numbers, not positions within your selected data.

  • Text values in cells: SUMPRODUCT ignores text in the multiplication-based calculation shown above, but numbers stored as text may not be included as expected.

  • Inserted rows: If you insert rows within your data, review the range and confirm that the alternate-row pattern still matches your requirements.

Final Thoughts

Summing every alternate row in Excel is much easier when you use the MOD, ROW and SUMPRODUCT functions together.

For most situations, this formula is a good starting point:

=SUMPRODUCT((MOD(ROW(B2:B100)-ROW(B2),2)=0)*B2:B100)

It adds values from alternate positions, beginning with B2, and can handle much larger datasets without manually selecting individual cells.

If you need a more visible and manageable solution, the helper column method with SUMIF is another excellent option.

Once you understand these formulas, you can use the same technique to calculate alternate-row totals in sales reports, expense sheets, attendance records and many other Excel worksheets.

Frequently Asked Questions (FAQs)

1. How do I sum every other row in Excel?

Use =SUMPRODUCT((MOD(ROW(B2:B100)-ROW(B2),2)=0)*B2:B100) to add every alternate position starting from B2.

2. How do I sum alternate rows starting from the second row?

Use =SUMPRODUCT((MOD(ROW(B2:B100)-ROW(B2),2)=1)*B2:B100).

3. Can I sum alternate rows without using VBA?

Yes. You can use SUMPRODUCT, MOD and ROW functions without writing any VBA code.

4. Can I sum alternate rows in Excel automatically?

Yes. SUMPRODUCT automatically identifies and adds alternate positions within your specified range.

5. Does the alternate-row formula work in Excel 365?

Yes. These SUMPRODUCT, MOD and ROW formulas work in Excel 365 and many earlier Excel versions.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content