# Why does SUM return 0 in Excel?

The numbers are stored as text, and SUM skips text. Convert them inside the formula: =SUMPRODUCT(--B2:B8).

**Excel:** `=SUMPRODUCT(--B2:B8)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Invoice | Amount (imported) |  | Total |
| INV-4410 | 1250 |  |  |
| INV-4411 | 980.5 |  |  |
| INV-4412 | 2310 |  |  |
| INV-4413 | 45.75 |  |  |
| INV-4414 | 12000 |  |  |
| INV-4415 | 615 |  |  |
| INV-4416 | 87.2 |  |  |

Formula in D2: `=SUMPRODUCT(--B2:B8)`

Result: $17,288.45

## 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.

## Other ways

- `=SUM(VALUE(B2:B8))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-returns-zero