Why does SUM return 0 in Excel?
Formula (Excel and Google Sheets)
=SUMPRODUCT(--B2:B8)The numbers are stored as text, and SUM skips text. Convert them inside the formula: =SUMPRODUCT(--B2:B8).
- Result on the example
- $17,288.45 in
D2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
The amounts were imported as text. SUM skips text, so with nothing but text in the range it returns 0. The double minus converts each value into a number inside the formula, and SUMPRODUCT adds the converted values without needing an array formula.
=SUMPRODUCT(--B2:B8)Result17288.45
B2:B87 rows × 1 col: "1250", "980.5", "2310", "45.75", …A range: 7 cells in one column.
-B2:B8{7 rows × 1 col} -1250, -980.5, -2310, -45.75, …Makes the value negative.
--B2:B8{7 rows × 1 col} 1250, 980.5, 2310, 45.75, …Makes the value negative.
SUMPRODUCT(--B2:B8)17288.45SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--B2:B8{7 rows × 1 col} 1250, 980.5, 2310, 45.75, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--B2:B8). We ran it in Google Sheets mode on the same data and it returns $17,288.45.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(VALUE(B2:B8)) | $17,288.45 | VALUE converts the whole range. Google Sheets needs ARRAYFORMULA around it; Excel 2019 and earlier need Ctrl+Shift+Enter. |
Common mistakes
- A formula returning "" in the range makes the conversion fail with #VALUE!. Truly empty cells are fine.
- Formatting the column as Number doesn't convert text already there. Use Data, Text to Columns, Finish, or the Convert to Number warning.
- Amounts with currency codes or units, like "USD 1,250", need cleaning with SUBSTITUTE before they convert.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does SUM return 0
D2, total the imported amounts in B2:B8, which are stored as text.Questions
How do I spot numbers stored as text?
They align left, often show a green triangle, and =ISTEXT(B2) is TRUE.
How do I convert the column for good?
Select it and use Data, Text to Columns, Finish, or a helper column with =VALUE(B2) pasted back as values.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.