Formula Errors

How to Fix SUMPRODUCT Displaying Formula or #VALUE! Error in Excel

WPS Editor Oct 7, 2026 869 views

Question details

The SUMPRODUCT formula fails to calculate, either displaying the raw formula text in the cell or returning a #VALUE! error instead of the mathematical result.

How to Fix SUMPRODUCT Displaying Formula or #VALUE! Error in Excel

Product: Excel
Device & OS: not provided
Scenario: Multiplying and summing specific data ranges, such as calculating total wire lengths by cable quantities.
Observed behavior: The cell shows the actual formula (e.g., =SUMPRODUCT(C4:C88,F4:F88)) instead of calculating, or it outputs a #VALUE! error.

Before you start

Before troubleshooting, ensure that the ranges used in your SUMPRODUCT formula have the exact same number of rows and columns, as mismatched array sizes will immediately cause a calculation error.

Solution 1Recommended

Resolve the #VALUE! Error by Converting Text to Numbers

The #VALUE! error in SUMPRODUCT is primarily caused by non-numeric characters, hidden spaces, or numbers stored as text within the calculation ranges.

SUMPRODUCT multiplies corresponding components in the given arrays and returns the sum of those products. If it encounters a text string instead of a number, the mathematical operation fails and triggers a #VALUE! error.

Locate numbers stored as text

Highlight your data ranges (e.g., C4:C88 and F4:F88). Look for cells aligned to the left or cells containing a small green triangle in the top-left corner.

Convert text to numbers

Click the warning icon that appears next to the selected cells and choose 'Convert to Number' from the dropdown menu.

Remove hidden spaces

If errors persist, highlight the ranges, press Ctrl + H to open Find and Replace, type a single space in the 'Find what' field, leave 'Replace with' blank, and click 'Replace All'.

Recalculate the formula

Double-click the cell containing your =SUMPRODUCT(C4:C88, F4:F88) formula and press Enter to refresh the calculation.

Resolve the #VALUE! Error by Converting Text to Numbers

Handling Blank Cells: Truly blank cells are treated as zeros by SUMPRODUCT, which is fine. However, cells containing empty strings ("") returned by other formulas will cause a #VALUE! error.

Solution 2

Fix the Cell Displaying the Formula as Plain Text

If the cell simply shows the formula text instead of attempting to calculate it, the cell formatting is incorrect or the global 'Show Formulas' setting is active.

Advanced Spreadsheet Tool

Calculate Complex Arrays Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT. It offers seamless compatibility with Excel formulas and built-in error checking to help you manage large datasets without formatting glitches.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Enter the SUMPRODUCT formula: Select the cell where you want the result to appear and type =SUMPRODUCT(.
3. Select your arrays: Highlight your first data range (e.g., C4:C88), type a comma, highlight the second range (e.g., F4:F88), and type a closing parenthesis.
4. Calculate the result: Press Enter. WPS Spreadsheet will instantly multiply the matching rows and return the total sum.

100% compatible with Microsoft Excel .xlsx files and array formulasSmart error-checking to quickly identify and convert text-formatted cellsFree, lightweight, and features an intuitive tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Can SUMPRODUCT calculate ranges of different sizes?

No. If the arrays (ranges) inside the SUMPRODUCT function do not have the exact same number of rows and columns, the function cannot map the corresponding cells together and will return a #VALUE! error.

Why does SUMPRODUCT return #VALUE! even if the cells look like numbers?

Invisible characters, such as trailing spaces or hidden apostrophes, force the spreadsheet to interpret the cell as text. You can use the VALUE function or the Find and Replace tool to clean these hidden characters from your data.

How do I ignore text values in a SUMPRODUCT calculation?

If your arrays contain text that you cannot remove, try using the double negative (--) operator inside the formula to coerce boolean values, or structure the function as =SUMPRODUCT(A1:A10, B1:B10) with a comma rather than an asterisk (*), which sometimes handles text more gracefully depending on the data structure.

Excel & Data Problems