Fix an Excel for the Web SUM Formula Returning Zero
Question details
The user needs a SUM formula in Excel for the Web to total imported or pasted values correctly.

- Product
- Excel for the Web
- Device & OS
- not provided
- Scenario
- Summing values that were imported or pasted into a workbook.
- Observed behavior
- The SUM formula returns zero; the source values may be stored as text or contain hidden characters.
Identify the cells included in the SUM formula and check whether the expected values use the decimal and thousands separators supported by the workbook's locale.
Check and convert values stored as text
Use this method when SUM ignores imported or pasted values because Excel does not recognize them as numbers.
In an empty cell, enter =ISNUMBER(A2), replacing A2 with a cell in the SUM range. FALSE indicates that the value is not stored as a number.
Select the source cell and inspect its contents in the formula bar. Remove any unexpected characters, then press Enter and check whether ISNUMBER returns TRUE.
In an empty helper column, enter =IFERROR(VALUE(SUBSTITUTE(A2,CHAR(160),"")),"") and fill the formula down. This removes nonbreaking spaces and attempts to convert each result to a number.
Enter a SUM formula over the helper column, such as =SUM(B2:B10). Check any blank conversion results for other hidden characters or mismatched decimal separators.
If the data came from a query or import, review its source and import settings so that future values are recognized as numbers.

Isolate the workbook or browser issue
Use this method if the values are already numeric or the formula works in another workbook.
Work with Spreadsheet Formulas in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks in WPS Spreadsheets to review values and formulas while moving between office suites.

Frequently Asked Questions
Why does SUM return zero when cells appear to contain numbers?
The values may be stored as text or contain hidden characters. Enter =ISNUMBER(A2) in an empty cell, replacing A2 with a source cell, to check whether Excel recognizes it as a number.
Will changing the cell format convert text to numbers?
Changing the format alone generally does not convert existing text values. Edit the cell to remove unwanted characters or use VALUE in a helper column to convert the text.
How can I remove a nonbreaking space from an imported value?
In a helper cell, enter =SUBSTITUTE(A2,CHAR(160),"") to remove nonbreaking spaces. Wrap it in VALUE, as in =VALUE(SUBSTITUTE(A2,CHAR(160),"")), to attempt a numeric conversion.
What should I try if the formula works in a different workbook?
Copy the affected cells to a new worksheet using Paste Special > Values, then test SUM again. You can also try another browser, clear the browser cache, or open the file in desktop Excel.




