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:
| Row | Sales Amount |
|---|---|
| 2 | 500 |
| 3 | 300 |
| 4 | 700 |
| 5 | 200 |
| 6 | 600 |
| 7 | 400 |
| 8 | 800 |
| 9 | 350 |
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.=0selects every second row, starting with B2.SUMPRODUCTadds 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)=0Step 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:
| Day | Sales |
|---|---|
| Monday | 1,500 |
| Tuesday | 2,000 |
| Wednesday | 1,800 |
| Thursday | 2,500 |
| Friday | 2,200 |
| Saturday | 3,000 |
| Sunday | 2,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.
| Method | Formula or Function | Best For |
|---|---|---|
| MOD + SUMPRODUCT | SUMPRODUCT | Flexible alternate-row calculations |
| MOD with 1 | SUMPRODUCT | Starting from the second position |
| ROW + MOD | SUMPRODUCT | Selecting even or odd worksheet rows |
| Individual references | SUM | Small datasets |
| Helper column | SUMIF | Large 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