Formula Errors

How to Fix SUMPRODUCT #VALUE! Error Caused by Blank Cells

Maira Mehtab Sep 28, 2026 871 views

Question details

The SUMPRODUCT formula returns a #VALUE! error because the referenced source column contains blank cells or blank formula results.

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating a weighted sum or multiplying arrays across a data range where some rows lack data in the 'Total Amount' column.
Observed behavior: Instead of calculating the total, the formula breaks and displays a #VALUE! error code.

Before you start

Verify that all arrays or ranges used in your SUMPRODUCT formula are exactly the same size. Additionally, check if the blank cells are truly empty or if they contain hidden spaces and formulas returning empty strings.

Solution 1Recommended

Replace Blank Cells with Zeros Using Go To Special

Use this method if the column contains empty cells that are typed manually rather than generated by a formula.

When the multiplication operator (*) is used inside SUMPRODUCT, it forces Excel or WPS Spreadsheet to treat all cell contents as numbers. Since a blank cell cannot be multiplied mathematically in this context, it causes a #VALUE! error. Replacing these empty gaps with the number zero immediately resolves the calculation.

Select the Target Range

Highlight the 'Total Amount' column or the specific range of cells that your SUMPRODUCT formula references.

Open the Go To Special Dialog

Press 'Ctrl + G' on your keyboard to open the 'Go To' window, then click the 'Special...' button at the bottom.

Select Blanks

Choose the 'Blanks' radio button and click 'OK'. This will highlight only the empty cells within your selected range.

Enter Zero

Without clicking anywhere else, type the number '0' on your keyboard.

Apply to All Selected Blanks

Press 'Ctrl + Enter' simultaneously. All previously blank cells will now contain a zero, and your SUMPRODUCT formula should calculate correctly.

Quick Fix: By filling blanks with zeros, the SUMPRODUCT formula has valid numerical values to multiply, completely eliminating the #VALUE! error.

Solution 2

Modify Source Formulas to Return Zero Instead of Blanks

Use this method if your 'Total Amount' column is populated by other formulas (like IF or VLOOKUP) that currently output an empty string ("").

Smart Formula Troubleshooting

Easily Fix Formula Errors with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools to quickly identify blank cells, trace formula errors, and accurately calculate complex arrays like SUMPRODUCT. Fix data inconsistencies seamlessly in a familiar, user-friendly interface.

1. Open Your Spreadsheet in WPS: Launch WPS Spreadsheet and open the file containing the #VALUE! error.
2. Locate Blank Cells: Navigate to the Home tab, click on 'Find and Replace', and select 'Go To'. Choose 'Blanks' to instantly select all empty cells in your data range.
3. Fill with Zeros: Type '0' and press Ctrl + Enter to fill the blanks, immediately resolving your SUMPRODUCT issue.

100% format and formula compatibility with Microsoft ExcelBuilt-in Find and Replace and 'Go To Special' features to quickly replace blanksAdvanced Error Checking features to identify the exact source of a #VALUE! errorFree to download and lightweight on system resources

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMPRODUCT return a #VALUE! error when there is text or a blank cell?

When you use the multiplication operator (*) to multiply arrays inside the SUMPRODUCT function, it expects strict numerical values. A formula-generated blank ("") or text string cannot be mathematically multiplied, causing the function to output a #VALUE! error.

Can I make SUMPRODUCT ignore blanks without changing them to zero?

Yes. Instead of using the multiplication sign (*), you can separate the arrays with commas (e.g., =SUMPRODUCT(A1:A10, B1:B10)). When using commas, the native SUMPRODUCT function automatically treats text and blank cells as zeros.

What if the cells look blank but replacing them doesn't fix the error?

The cells might contain hidden spaces instead of being truly blank. You can use the TRIM function to clean the data, or use the Find and Replace tool (Ctrl + H) to find spaces (' ') and replace them with nothing.

Excel & Data Problems