Excel SUM Not Working? 7 Fixes for Numbers Stored as Text (With Quick Diagnosis)
Summary: When SUM returns 0 or calculations are incorrect, the values in the column are likely text that looks like numbers. First check the type with ISTEXT/ISNUMBER, then use the appropriate method in this order: warning icon conversion → Paste Special (Multiply) → multiply by 1 → Text to Columns → NUMBERVALUE/VALUE → remove spaces and hidden characters. Finally, use SUBTOTAL to calculate accurate totals for filtered ranges.
1) Why does SUM return 0? — Quick diagnosis
Select sample cell A2 and use =ISTEXT(A2) and =ISNUMBER(A2) to check its type. If ISTEXT=TRUE, it is a text number. It is also highly likely to be text if the cell is left-aligned or has a small green triangle (error indicator) in the corner. If you formatted the data as a table (Ctrl+T), set the entire column’s number format to General. Add AutoSum with Alt+=, then immediately verify that the reference range is correct by showing formulas (Ctrl+`).
2) The 3 fastest fixes
- Error checking icon → Convert to Number: The safest option for beginners.
- Paste Special (Multiply): Copy 1 → select the target range → Ctrl+Alt+V → Multiply (M) → OK.
- Multiply by 1/add 0: Use
=A2*1or=A2+0, then paste values only.
3) Locale issues (commas/decimal points)
If data from an overseas ERP or CSV file appears as 1.234,56, VALUE may fail. In that case, use NUMBERVALUE to specify the symbols.
=NUMBERVALUE(A2, ",", ".") ' Decimal separator (,), thousands separator (.) version4) Remove hidden characters and spaces
Data from websites or systems often contains nonprinting characters. The formula below resolves most cases.
=VALUE(SUBSTITUTE(TRIM(CLEAN(A2)),CHAR(160),""))5) Accurate totals for filtered ranges
In reports that use filters, use SUBTOTAL or AGGREGATE instead of SUM to exclude hidden rows.
=SUBTOTAL(9, Plan[Amount]) ' 9=SUM (excludes filtered-out rows)
=AGGREGATE(9,7, Plan[Amount]) ' 7=ignore hidden rows/errors6) Checklist (avoid mistakes)
- Changing only the cell format to Number does **not change the value itself**. Use a function or operation to convert it.
- If calculation mode is set to Manual, press
F9to recalculate. - Creating a table (Ctrl+T) and applying data validation to the column helps prevent the problem from recurring.