The total says 0, the column is full (text numbers)
The problem
Problem statement
The monthly report shows 0 against a column of five invoices in B2:B6. The amounts came in from an export, and they are stored as text, so SUM sees nothing to add. Put the real total in the Correct total cell E2.
Convert the text to numbers inside the formula — retyping the column is not available to you.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Invoice | Amount | Report (wrong) | 0 |
| 2 | INV-901 | 1200 | Correct total | |
| 3 | INV-902 | 800 | ||
| 4 | INV-903 | 950 | ||
| 5 | INV-904 | 600 | ||
| 6 | INV-905 | 400 |
Notes
SUM ignores text, and an import that wraps numbers in quotes leaves the whole column dead to it. SUMPRODUCT(--range) coerces each value to a number, where -- is two unary minuses; SUM(VALUE(range)) does the same conversion explicitly. A quick tell in the sheet: text numbers sit left-aligned, real numbers sit right-aligned.
How to approach
Your answer goes in E2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is E2
- SUM returns 0 because the cells are text, not numbers
- You need to coerce the range to numbers first
- Shape: =SUMPRODUCT(--?) or =SUM(VALUE(?))
Official solutions
Show 2 solutions — try it yourself first
Approach 1: coerce with a double unary minus
=SUMPRODUCT(--B2:B6)
The two minuses turn each text number into a real number, and SUMPRODUCT adds them: 1200 + 800 + 950 + 600 + 400 = 3950. No helper column, no retyping.
Approach 2: VALUE inside SUM
=SUM(VALUE(B2:B6))
VALUE converts each cell explicitly and SUM adds the array. Same result, and it reads closer to what is actually happening — in Excel 2019 and older this one needs Ctrl+Shift+Enter.
FAQ
Why does SUM return 0 when the cells clearly contain numbers?
They are text, not numbers — usually an export or a paste from a web page. Coerce them with SUMPRODUCT(--range), SUM(VALUE(range)), or multiply by 1.
How can I spot text numbers without clicking every cell?
They align left. Real numbers align right by default, so a left-aligned number is the fingerprint of a text value.