Why Your Totals Are Wrong: Numbers Stored as Text

SUM silently ignores text. A column that looks entirely numeric can contain a few text entries, and your total will simply be wrong — no error message, and no indication in the total itself that anything was left out.

One formula tells you whether it's happening:

=COUNTA(D2:D500) - COUNT(D2:D500)

COUNTA counts anything; COUNT counts only numbers. Anything other than zero is how many cells are lying to you.

Fourteen invoices, five of them invisible to SUM. The total is understated by 7,735 and looks entirely reasonable.

Why this one matters more than the others

Every other error in Excel announces itself. #N/A, #REF!, #VALUE! — you can see them and go and fix them.

This one produces a plausible number. In the example above the sheet reports 10,247 where the true figure is 17,982 — understated by more than 40%.

The only hint is a small green triangle in the corner of five cells, and a difference in which side of the cell the figures sit on. Neither appears anywhere near the total, and plenty of people switch the triangles off because they find them distracting.

That figure gets copied into a report, quoted in a meeting, and used to make a decision. The error surfaces weeks later, if at all.

Spotting it in three seconds

Check	What tells you
Which side of the cell?	Numbers sit right, text sits left — regardless of number format
Select the column and read the status bar	No Sum shown, or a Sum lower than expected
Green triangle in the corner	Excel's own warning — though it doesn't appear on every case
=ISNUMBER(D2)	FALSE on the offenders
=COUNTA(range)-COUNT(range)	How many, in one number

Do the alignment check first. It costs nothing and catches most cases immediately — a column of right-aligned figures with three left-aligned ones stands out the moment you look for it.

Fixing it

The bulk fix

Select the column, then Data → Text to Columns → Finish.

It looks like it does nothing. It re-parses every cell in place, converting anything that can be a number into one. For a plain column of numeric text, this is the whole job and takes two seconds.

When the column must stay where it is

Paste Special multiplication converts in place without moving anything:

1. Type 1 into an empty cell and copy it.
2. Select the problem range.
3. Paste Special → Multiply → OK.
4. Delete the cell holding the 1.

Multiplying by one forces Excel to treat each entry as a number and store the result. Works on ranges Text to Columns can't handle in one go, such as several columns at once.

When the text isn't clean

Currency symbols, thousands separators from another locale, and spaces all defeat a straight conversion. Strip them first:

=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM($B2), ",", ""), CHAR(160), ""))

CHAR(160) is the non-breaking space, which TRIM does not remove and which arrives with anything copied from a web page. It's the reason a value that looks identical to its neighbour still won't convert.

Two formats that beat VALUE entirely

These come out of accounting and ERP systems, and they're worth knowing because the obvious fix fails on both.

The trailing minus

Older systems write negatives as 1234- rather than -1234. VALUE returns an error, and if you strip the minus you get a positive number — which is considerably worse than an error.

=IF(RIGHT(TRIM($B2),1)="-",
    -VALUE(LEFT(TRIM($B2), LEN(TRIM($B2))-1)),
    VALUE(TRIM($B2)))

⚠️ If an export contains trailing minuses and nobody notices, every negative becomes positive. A column of refunds then adds to your revenue instead of subtracting from it. This is the single most expensive version of this problem.

Parentheses for negatives

Accounting convention writes (1,234) for minus 1,234. As text, VALUE refuses it:

=IF(LEFT(TRIM($B2),1)="(",
    -VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM($B2),"(",""),")",""),",","")),
    VALUE(SUBSTITUTE(TRIM($B2),",","")))

Ugly, and it only has to be written once.

Check that you fixed it

Run the diagnostic again. COUNTA minus COUNT should now be zero.

Then check the total against something you can verify independently — the figure on the original report, or the statement the data came from. A total that changed by exactly the amount you expected is a fix; one that changed by an amount you can't account for means something else is wrong too.

Stopping it recurring

• Import properly. Data → Get Data → From Text/CSV → Transform Data, and set each numeric column's type explicitly. Double-clicking a CSV is where most of this originates.
• Don't format columns as Text unless they hold codes. A "just in case" Text format on a numeric column guarantees this problem.
• Paste as values when bringing figures in from a web page or email, so formatting doesn't travel with them.
• Put the diagnostic in the sheet. One cell holding COUNTA-COUNT beside the total, and you'll never be caught by it again.

That last one is the highest-value habit in this article. It costs one cell and it turns a silent error into a visible one.

Does this work in Google Sheets?

The same problem exists, and Sheets is slightly more forgiving — it converts numeric text in some arithmetic where Excel refuses. That inconsistency is its own hazard: a file that totals correctly in Sheets and incorrectly in Excel, with identical contents.

COUNTA, COUNT, ISNUMBER and VALUE all behave the same. Sheets has no Text to Columns → Finish trick; multiply the range by 1, or use Data → Split text to columns.

When it goes wrong

Text to Columns didn't convert anything

The cells are formatted as Text. Set the column to General first, then run it again. Formatting is never retroactive — the same rule that makes formulas display as text.

VALUE returns #VALUE! on something that looks like a number

A currency symbol, a stray space, a non-breaking space, or one of the two accounting formats above. Strip before converting.

The total changed but still looks wrong

Check for trailing minuses. If negatives converted as positives, the total moved in the wrong direction by twice their value.

Some cells converted and some didn't

The stubborn ones have something extra in them. =LEN() against a working neighbour finds the difference.

It comes back every month

The import is the cause. Set the column types once in Power Query and refresh instead of re-importing.

Common questions

Why doesn't SUM just warn me?

Because ignoring text is deliberate — it's what lets SUM(A1:A20) work when A1 holds a header. Useful behaviour, dangerous side effect.

Does SUMIFS have the same problem?

Yes, and worse — text values are skipped in the sum and may fail to match the criteria, so a row can vanish from a conditional total twice over.

Will the green triangle always appear?

No. Excel's error checking catches many cases but not all, and people routinely turn it off because it's noisy. Don't rely on it.

Is there a way to make SUM include text numbers?

=SUMPRODUCT(VALUE(B2:B15)) will do it, and you shouldn't. It papers over data that's still wrong for every other formula that touches it. Fix the column.

How do I check a whole workbook at once?

Put COUNTA-COUNT against every numeric column on a small audit sheet. Ten minutes to build, and it's the fastest routine check there is for a file you didn't create.

The short version

• SUM ignores text and reports a confident wrong answer.
• =COUNTA(range)-COUNT(range) — anything but zero is the number of bad cells.
• Numbers sit right, text sits left. Check the alignment first.
• Data → Text to Columns → Finish fixes a clean column instantly.
• Paste Special → Multiply by 1 converts in place.
• Strip commas, currency symbols and CHAR(160) before converting.
• Trailing minus and parenthesised negatives need their own formulas — and getting them wrong flips the sign.
• Keep the diagnostic in the sheet, next to the total.

Next steps: this is step three of cleaning an export, and the same mismatch is what stops two lists matching and produces #N/A on a lookup that should work. If the import mangled your reference codes as well as your figures, that damage is harder to undo.