Function Problems

How to Sum Products Only for Odd Rows in Excel

Steven ParkerSteven Parker Nov 10, 2026 869 views

Question details

The user needs to multiply values across two columns and sum the resulting products exclusively for the odd-numbered rows.

How to Sum Products Only for Odd Rows in Excel
Product
Excel
Device & OS
not provided
Scenario
Calculating a conditional sum of products where only alternating rows (specifically odd rows) should be included in the total.
Observed behavior
The user requires a formula to selectively calculate and sum products based on the odd-numbered index of the worksheet rows.
Before you start

Ensure your data ranges contain only numbers or blank cells, as text values may result in an error when multiplied. Verify the exact range of your data columns before applying the array formula.

Solution 1Recommended

Use a Combination of SUM, ISODD, and ROW Functions

Combine the SUM function with ISODD and ROW to evaluate and multiply only the rows that have an odd index.

This approach evaluates the row numbers of the specified range. The ISODD function returns TRUE (which Excel evaluates as 1) for odd rows and FALSE (evaluated as 0) for even rows. When multiplied by the column values, only the products from the odd rows contribute to the final sum.

1
Select the destination cell

Click on the blank cell where you want the final calculated total to appear.

2
Enter the formula

Type the formula =SUM(A3:A102*B3:B102*ISODD(ROW(B3:B102))) into the formula bar. Adjust the range A3:B102 to match your actual data layout.

3
Execute the calculation

Press Enter to calculate the formula. If you are using an older version of Excel, you may need to press Ctrl + Shift + Enter to evaluate it properly as an array formula.

Use a Combination of SUM, ISODD, and ROW Functions
Handling Empty Cells: This formula gracefully handles blank cells by treating them as zeros, ensuring your total sum remains accurate without triggering calculation errors.
WPS Spreadsheet Solutions

Calculate Conditional Sums Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, including SUM, ISODD, and ROW. You can seamlessly execute complex conditional calculations on alternating rows just like in Microsoft Excel.

  1. 1. Open your data in WPS: Launch WPS Spreadsheet and open your workbook containing the data columns to be multiplied.
  2. 2. Input the array formula: Select a blank cell and enter =SUM(A3:A102*B3:B102*ISODD(ROW(B3:B102))) into the formula bar.
  3. 3. Calculate the result: Press Enter to instantly sum the products of your odd-numbered rows.
Full support for advanced array formulas to sum specific rows100% compatible with Microsoft Excel (.xlsx) file formatsLightweight software with rapid data processing speedsFamiliar user interface for a seamless transition
microsoft office alternative - wps office

Frequently Asked Questions

How can I sum products for even rows instead of odd rows?

You can replace the ISODD function with the ISEVEN function in your formula. The updated formula will be =SUM(A3:A102*B3:B102*ISEVEN(ROW(B3:B102))).

Why am I getting a #VALUE! error when using this formula?

A #VALUE! error usually occurs if there are text strings or non-numeric characters in your data ranges. Ensure that your multiplied ranges contain only valid numbers or blank cells.

Can I use the SUMPRODUCT function instead of SUM for this task?

Yes, you can use the SUMPRODUCT function, which is designed to handle arrays natively without requiring Ctrl+Shift+Enter in older versions. The alternative formula is =SUMPRODUCT(A3:A102, B3:B102, --ISODD(ROW(B3:B102))).

Does this formula work if my data starts on an even row but I want to alternate?

The ISODD formula evaluates the absolute row number of the worksheet. If your data starts on an even row and you want to sum alternating relative rows, you will need to adjust the logic, such as using the MOD function to check the remainder of the row index.