Formula Errors

Fix WPS Spreadsheets SUMPRODUCT #DIV/0! Errors Caused by Blank Cells

John WilsonJohn Wilson Nov 13, 2026 869 views

Question details

The user needs to resolve #DIV/0! errors in a SUMPRODUCT formula that performs division, caused by blank or zero values in the divisor range, while still including valid rows in the calculation.

Fix WPS Spreadsheets SUMPRODUCT #DIV/0! Errors Caused by Blank Cells
Product
WPS Spreadsheets
Device & OS
not provided
Scenario
A user is building a SUMPRODUCT formula that multiplies criteria and divides arrays, but blank or zero cells in the divisor range make some rows invalid.
Observed behavior
The formula returns #DIV/0! and needs to replace invalid division results with zero so the remaining valid rows can contribute normally.
Before you start

Check that all SUMPRODUCT ranges are the same size and point to the intended data. Identify the divisor range, such as E2:E3, because blank or zero cells there cause the #DIV/0! error before IFERROR can handle it.

Solution 1Recommended

Wrap the Division Portion in IFERROR to Return 0 for Invalid Rows

Use this when blank or zero cells in the divisor cause #DIV/0!, and you want only the invalid division results to become 0 while the rest of SUMPRODUCT continues.

The example assumes A, B, and D are criteria columns, C is the numerator, and E is the divisor. IFERROR catches the division error inside the array calculation and returns 0 for those positions.

1
Open your workbook in WPS Spreadsheets

Open the worksheet that contains the SUMPRODUCT formula returning #DIV/0!.

2
Select the formula cell

Click the cell with the SUMPRODUCT formula so the formula appears in the formula bar.

3
Wrap the division expression with IFERROR

In the formula bar, change the division part like (C2:C3/18)/(E2:E3/12)^2 to IFERROR((C2:C3/18)/(E2:E3/12)^2,0). The full formula becomes =SUMPRODUCT(IFERROR((A2:A3="slab")*(B2:B3="house")*(D2:D3="10M")*(C2:C3/18)/(E2:E3/12)^2,0))*2*1.05.

4
Apply the formula

Press Enter. WPS Spreadsheets recalculates the formula and returns 0 for rows where the divisor is blank or zero, eliminating #DIV/0!.

Wrap the Division Portion in IFERROR to Return 0 for Invalid Rows
Tip: If the final result must be rounded up, wrap the entire calculation in ROUNDUP as shown in the next solution.
WPS Spreadsheets formula troubleshooting

Fix SUMPRODUCT #DIV/0! Errors in WPS Spreadsheets

Use WPS Spreadsheets to correct the formula directly: wrap the division portion of SUMPRODUCT in IFERROR so invalid rows return 0, then apply ROUNDUP if your final total must round up.

  1. 1. Open the workbook: Launch WPS Spreadsheets and open the .xlsx file that contains the #DIV/0! formula.
  2. 2. Select the formula cell: Click the SUMPRODUCT cell so its formula appears in the formula bar.
  3. 3. Wrap the division in IFERROR: Replace the division expression with IFERROR(division expression,0). For example: IFERROR((C2:C3/18)/(E2:E3/12)^2,0).
  4. 4. Press Enter: Press Enter to recalculate. The error disappears and valid rows contribute normally.
  5. 5. Optional: round up the result: If your result must be rounded up, change the formula to =ROUNDUP(SUMPRODUCT(...),0).
Resolves #DIV/0! errors in SUMPRODUCT while keeping valid rows in the calculationFull Excel formula compatibility for SUMPRODUCT, IFERROR, and ROUNDUPOpens and saves .xlsx workbooks without losing formulas or formattingFree, lightweight, and familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMPRODUCT return #DIV/0! when my divisor range contains blank cells?

A blank cell evaluates as 0 in a division expression. In WPS Spreadsheets and Excel, dividing by 0 produces #DIV/0!, so any row with a blank or zero in the divisor range makes that part of the SUMPRODUCT array invalid unless it is wrapped in IFERROR.

Can I use IFERROR inside SUMPRODUCT in WPS Spreadsheets?

Yes. WPS Spreadsheets supports Excel-compatible functions such as SUMPRODUCT and IFERROR. Edit the formula bar and wrap only the division expression inside IFERROR to return 0 for invalid rows, then press Enter to recalculate.

Does IFERROR hide other formula errors when used with SUMPRODUCT?

IFERROR catches all error types in the expression it wraps, not only #DIV/0!. To avoid hiding unrelated issues, wrap only the division portion of the SUMPRODUCT formula rather than the whole formula.

How do I round up the final SUMPRODUCT result after fixing the error?

Add ROUNDUP around the complete SUMPRODUCT calculation and any multipliers, for example =ROUNDUP(SUMPRODUCT(IFERROR((C2:C3/18)/(E2:E3/12)^2,0)),0). Use 0 as the second argument to round up to the nearest whole number.