Why Your Excel SUM Is Not Working

That silent behaviour is what makes this so confusing. A total that is simply wrong looks like an Excel fault, when Excel is doing precisely what it is documented to do.

1. Numbers stored as text

SUM adds numeric values and ignores text, logical values and empty cells. It does not warn you, so a column where half the values are text produces a plausible but wrong total.

How to spot it:

• Alignment. Numbers align right by default, text aligns left.
• The green triangle in the cell corner, with a warning reading "Number Stored as Text".
• =COUNT(A1:A10) versus =COUNTA(A1:A10). COUNT counts numbers only; COUNTA counts anything non-empty. Different answers mean some values are text.
• =ISNUMBER(A1) on a single cell returns TRUE or FALSE outright.

Fixes, fastest first:

1. The warning triangle. Select the range, click the triangle, choose Convert to Number. Handles the whole selection at once.
2. Text to Columns. Select the column, Data → Text to Columns, click Finish immediately. Re-parses every cell as a number.
3. Paste Special multiply. Type 1 into an empty cell and copy it. Select your range, right-click → Paste Special → Multiply. Multiplying by 1 forces numeric conversion in place.
4. In the formula. =SUMPRODUCT(--(A1:A10)) — the double negative coerces text to numbers without changing the source data.

2. Why the conversion sometimes fails

If Convert to Number is greyed out or has no effect, something inside the value prevents it being read as a number:

• Spaces. " 1234" is text. =TRIM(A1) removes ordinary spaces.
• Non-breaking spaces from a web or PDF paste — character 160, invisible, and TRIM does not touch it. Use =TRIM(SUBSTITUTE(A1,CHAR(160),"")).
• Currency symbols or thousands separators that do not match your regional settings — "1,234.56" on a machine expecting "1.234,56".
• A trailing minus sign — "1234-" instead of "-1234", standard in some mainframe exports.

Clean the text first, then convert. Our guide to removing spaces in Excel covers the invisible characters in detail.

3. The total ignores hidden or filtered rows

Not a fault, but it surprises people: SUM includes hidden and filtered-out rows. Filter a list and the SUM at the bottom does not change, because the rows still exist.

To total only what is visible, use SUBTOTAL:

Formula	Behaviour
=SUM(A1:A100)	Includes hidden and filtered rows
=SUBTOTAL(9, A1:A100)	Excludes filtered-out rows, includes manually hidden ones
=SUBTOTAL(109, A1:A100)	Excludes both filtered and manually hidden rows

109 is the one you usually want. The difference between 9 and 109 catches people out constantly: 9 respects a filter but not rows you hid by right-clicking.

4. The formula shows instead of a result

If the cell displays =SUM(A1:A10) as text rather than a total, this is not a SUM problem — the cell was formatted as Text before the formula was typed, or Show Formulas is toggled on.

See why an Excel formula is not calculating, which covers both.

5. The status bar total has disappeared

A separate complaint with the same underlying cause. Selecting a range normally shows Average, Count and Sum along the bottom of the window.

If Sum is missing there, either the selected cells are text (Count appears but Sum does not), or the status bar item is switched off — right-click the status bar and tick Sum.

This makes the status bar a useful quick test: select your column and see whether a Sum appears at all.

Quick diagnosis

Symptom	Likely cause
Returns 0	Every value is text
Total too low	Some values are text
Does not change when filtered	Correct — use SUBTOTAL(109, …)
Shows the formula as text	Cell formatted as Text, or Show Formulas on
Never updates	Calculation set to Manual
No Sum in the status bar	Values are text, or the item is switched off

Frequently asked questions

Why is my Excel SUM not working?

Nearly always because the values are text rather than numbers, and SUM ignores text without warning. Check whether they are left-aligned, and compare =COUNT() with =COUNTA() over the range.

Why does SUM return 0?

Every value in the range is text. Select the range, click the warning triangle and choose Convert to Number, or use Data → Text to Columns → Finish.

Why is my total too low?

Some values are numbers and some are text — SUM adds the numbers and silently skips the rest. =COUNT() tells you how many are actually numeric.

Why doesn't my SUM change when I filter?

SUM includes hidden and filtered rows by design. Use =SUBTOTAL(109, A1:A100) to total only visible cells. The 9 variant respects filters but not manually hidden rows.

Convert to Number is greyed out — what now?

Something in the value blocks conversion: spaces, non-breaking spaces from a web paste, a currency symbol that does not match your region, or a trailing minus sign. Clean with =TRIM(SUBSTITUTE(A1,CHAR(160),"")) first.

How do I sum text-formatted numbers without changing the data?

=SUMPRODUCT(--(A1:A10)). The double negative coerces text to numbers inside the formula, leaving the source cells untouched.

Why has the Sum disappeared from the status bar?

Either the selected values are text, so there is nothing numeric to total, or the item is switched off — right-click the status bar and tick Sum.

Conclusion

Check the alignment, then run =COUNT() over the range. Between them they identify the text-versus-number problem in seconds, and that is the cause in the overwhelming majority of cases.

If the total is right but does not respond to filtering, nothing is wrong — you want SUBTOTAL(109, …) rather than SUM.

Related: why an Excel formula is not calculating covers formulas showing as text, and removing spaces in Excel covers the characters that block conversion.