# Why is my SUM formula returning 0 or too small a total in Excel

SUM skips numbers stored as text, so convert them while adding with `=SUMPRODUCT(--B2:B7)` and then fix the column for good.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Item | Amount (imported) |  | Total |
| Consulting | 1250 |  |  |
| Travel | 385.40 |  |  |
| Software | 899 |  |  |
| Hardware | 2,140 |  |  |
| Training | 450 |  |  |
| Licenses | 1,075 |  |  |

Formula in E1: `=SUMPRODUCT(--B2:B7)`

Result: $6,199.40

## How it works

Imported or pasted numbers often arrive as text: they sit on the left of the cell and may show a green triangle. SUM ignores them, so here it adds only 899 and 450. The double minus converts each value to a real number, text or not, and SUMPRODUCT adds the results.

## Other ways

- `=SUM(VALUE(B2:B7))`: Excel 365 calculates the array directly; older versions need Ctrl+Shift+Enter and Google Sheets needs ARRAYFORMULA.
- `=SUM(B2:B7)`: What a plain SUM sees. (Adds only the two real numbers: 1,349.)

## Common mistakes

- A total of exactly 0 usually means every value is text; a total that is too small means some are. Check with `=ISTEXT(B2)`.
- Converting inside the formula hides the problem. Fix the column with Data > Text to Columns > Finish, or select it and use the warning icon's Convert to Number.
- Text with letters ("n/a") can't be converted and gives #VALUE!. Clear or replace those cells first.

## Questions

**Why would SUM show 0 even though the cells have numbers?**

Either the numbers are text, or calculation is set to Manual (Formulas > Calculation Options > Automatic), or the formula sits in its own range and is a circular reference.

**How do I convert a whole column of text numbers?**

Type 1 in an empty cell, copy it, select the column, then Paste Special > Multiply.

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-formula-returns-zero