Function Problems

How to Exclude Alternating Rows in Excel Using SUMPRODUCT

Adam Davis Sep 25, 2026 869 views

Question details

The user needs to multiply values across two columns and sum the results while ignoring specific alternating rows (either all odd or all even rows).

How to Exclude Alternating Rows in Excel Using SUMPRODUCT

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating a conditional sum of products where periodic rows must be excluded from the calculation array.
Observed behavior: The user requires a formula combination using SUMPRODUCT to conditionally filter rows before multiplication and summation are executed.

Before you start

Ensure your data ranges are identically sized and do not contain error values, as errors in the referenced columns will cause the SUMPRODUCT formula to return an error.

Solution 1Recommended

Use SUMPRODUCT with MOD and ROW Functions

Combine SUMPRODUCT with MOD and ROW to conditionally calculate sums of products for only even or odd rows.

The SUMPRODUCT function natively multiplies corresponding components in arrays and returns the sum of those products. By nesting the MOD and ROW functions within it, you can create a True/False array that acts as a filter, effectively zeroing out the rows you want to exclude.

Select the target cell

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

Enter the formula to include even rows

Type =SUMPRODUCT((MOD(ROW(A2:A101),2)=0)*A2:A101*B2:B101) into the formula bar. This includes only even rows and excludes odd rows. Adjust the data ranges (A2:A101 and B2:B101) to match your actual worksheet data.

Modify the formula for odd rows

If you need to include only odd rows (excluding even rows), change the =0 in the formula to =1. The formula will become =SUMPRODUCT((MOD(ROW(A2:A101),2)=1)*A2:A101*B2:B101).

Execute the formula

Press the Enter key to apply the array logic and view your conditional sum product result.

Use SUMPRODUCT with MOD and ROW Functions

Understanding the Array Logic: The expression (MOD(ROW(),2)=0) evaluates to an array of 1s (True) and 0s (False). When multiplied by your data arrays, it turns the values of the excluded rows into zeros before they are summed up.

Advanced Spreadsheet Solution

Calculate Complex Array Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT, ROW, and MOD. You can easily calculate conditional sums and exclude alternating rows just as you would in Microsoft Excel, entirely for free.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx data file.
2. Select a calculation cell: Click on the blank cell where you want to display the final result.
3. Enter the SUMPRODUCT formula: Type your conditional formula, such as =SUMPRODUCT((MOD(ROW(A2:A101),2)=0)*A2:A101*B2:B101), directly into the formula bar.
4. Get the result instantly: Press Enter to instantly process the array filter and calculate the conditional sum of products.

Fully compatible with Microsoft Excel formulas and .xlsx files.Lightweight software with incredibly fast calculation speeds for large datasets.Built-in function wizard to easily construct complex SUMPRODUCT formulas.Free, comprehensive alternative to Microsoft Excel with an intuitive interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use SUMPRODUCT to exclude rows based on specific text instead of alternating rows?

Yes. Instead of using MOD and ROW, you can use a logical condition like =SUMPRODUCT((C2:C101<>"ExcludeText")*A2:A101*B2:B101) to skip rows where column C contains a specific text string.

Why does my SUMPRODUCT formula return a #VALUE! error?

This error typically occurs if the arrays referenced in the formula (such as A2:A101 and B2:B101) are not the exact same size, or if you are attempting to multiply text strings without converting or ignoring them properly.

Does this formula work the same way in WPS Office as it does in Excel?

Absolutely. WPS Spreadsheet shares full formula compatibility with Microsoft Excel. It supports the exact same SUMPRODUCT, MOD, and ROW syntax, ensuring your array formulas work seamlessly across both platforms.

How can I exclude every third row instead of alternating rows?

To exclude every third row, simply change the divisor in the MOD function. For example, use MOD(ROW(A2:A101),3)<>0 inside your SUMPRODUCT condition to keep the first two rows and exclude the third in every block of three rows.

Excel & Data Problems