Excel SUM Not Working? Five Causes, In Order

When Excel's SUM returns 0, or a total that is clearly too small, the formula is almost never the problem. One of five things is: the numbers are stored as text (SUM skips text, so the column adds up to nothing), calculation is set to Manual, the range stops before the rows you added, the cells hide an invisible character such as a non-breaking space, or the total sits inside its own range and created a circular reference. Test them in that order. Type =ISNUMBER(A2) next to one of the "numbers": if it says FALSE, you have found it, and the fix is Convert to Number or a helper column with VALUE. If it says TRUE, press F9, and keep going down the list below.

The order matters because the first cause is the most common, and each later test only makes sense once the earlier ones have been ruled out. Checking for a circular reference when your column is full of text wastes the minute you were trying to save.

Here is the whole diagnosis on one screen. Each row gives the clue you can see, a single formula or keypress that confirms it, and the repair.

Test	Cause	What you see	One-cell test	Repair
1	Numbers stored as text	Values sit on the left of the cell, often with a small green triangle in the corner. Selecting them shows only a Count on the status bar, no Sum.	`=ISNUMBER(A2)` returns FALSE, `=ISTEXT(A2)` returns TRUE	Convert to Number from the warning icon, or `=VALUE(A2)` in a helper column, or Data, Text to Columns, Finish
2	Manual calculation	You change a value and the total does not move. The status bar may say Calculate.	Press F9. If the total changes, this is it.	Formulas tab, Calculation Options, Automatic
3	Range stops short	The total is too small rather than zero. New rows at the bottom are not counted.	Click the total and look at the coloured border: it ends above your last row.	Extend the range, or turn the data into a table so it grows on its own
4	Invisible characters	Test 1 said text, but Convert to Number and `VALUE` both fail or return `#VALUE!`	`=CODE(RIGHT(A2))` returns 160, or `=LEN(A2)` is longer than the digits you can see	`=VALUE(SUBSTITUTE(A2,CHAR(160),""))` in a helper column
5	Circular reference	The status bar says Circular References, and the total shows 0 or a stale value	Formulas tab, Error Checking, Circular References lists the cell	Move the total out of the range it sums

One shortcut before you start: glance at the status bar at the bottom of the window. If it already says Circular References or Calculate, skip straight to that row.

Why does SUM return 0 when the cells clearly hold numbers?

Because the cells hold something that looks like a number to you and is text to Excel. Microsoft's own SUM function reference states the rule plainly: SUM ignores text values and returns the sum of just the numeric values. If every value in the range is text, the sum of the numeric ones is zero, and zero is what you get. No error, no warning in the formula cell, just a confident 0.

That silence is the reason this problem eats afternoons. An error like #VALUE! tells you something is wrong. A zero looks like an answer.

Text numbers arrive in a few predictable ways:

Exports. Accounting packages, CRMs, banks and web reports often write every field as a string, so 1240 arrives as the text "1240". If the file was a CSV, the same import settings that cause it also cause dropped leading zeros and garbled accents, and the guide to CSVs that open wrong in Excel covers how to import it properly the first time.
Copy and paste from a web page or PDF, which drags formatting and stray characters along with the digits.
Cells formatted as Text before anyone typed in them. Anything entered afterwards is stored as text, including formulas. If your SUM cell literally displays =SUM(A2:A20) instead of a result, that cell is formatted as Text. Change it to General, click into the cell, and press Enter.
An apostrophe prefix. '450 displays as 450 and is stored as text on purpose.

How do you tell numbers stored as text from real numbers?

Three clues, from weakest to strongest.

Alignment. With default formatting, Excel aligns real numbers to the right of the cell and text to the left. A column of figures hugging the left edge is a strong hint. It is only a hint, because someone may have left-aligned real numbers by hand.

The green triangle. Excel's background error checking marks text-stored numbers with a small green triangle in the top-left corner of the cell. Select the cell and a warning icon appears beside it. Microsoft's page on how to convert numbers stored as text to numbers notes that if you never see this alert, background error checking may be switched off: File, Options, Formulas, then tick Enable background error checking under Error Checking.

The one-cell test. This is the one to trust. In an empty cell, type:

=ISNUMBER(A2)

TRUE means Excel sees a number. FALSE means it does not, whatever the cell looks like. =ISTEXT(A2) gives the mirror answer. Copy the test down beside the whole column and you will often find the column is mixed: most values real, a handful of text ones pasted in later. That is the case where SUM does not return 0 but returns a total that is quietly too low, which is worse, because nobody questions it.

How to repair it

Pick by the size of the job.

A few cells. Select them, click the warning icon, choose Convert to Number. You can select a whole block of flagged cells first and convert them in one go.
A column you want to keep formula-free. Add a helper column with =VALUE(A2), fill it down, copy it, then paste it back over the original as values only (Home, Paste, Paste Special, Values, or Ctrl+Shift+V). Delete the helper. This is the route Microsoft documents on the convert page, and it works identically in Excel for the web.
A whole column in one pass. Select the column, go to Data, Text to Columns, and press Finish without changing anything. Excel re-parses every cell as if importing it fresh, and text that looks numeric comes out as numbers. The Text to Columns walkthrough explains why this works and the one situation where it overwrites neighbouring columns.

One thing that does not work: selecting the column and changing its number format from Text to Number or General. Formatting changes how a value is displayed, not what type it is stored as. The cells stay text until something re-enters them.

Why does =A1+A2 work when SUM doesn't?

This is the question that makes people think Excel is broken, and it is the single best diagnostic clue you can get.

The two formulas treat text differently. The plus operator tries to convert its inputs to numbers before adding them, so the text "450" becomes the number 450 and the arithmetic succeeds. SUM given a range does the opposite: it skips anything that is not already a number. So =A1+A2+A3 returns the right answer while =SUM(A1:A3) over the same cells returns 0.

If you have seen exactly this, stop looking at causes 2 to 5. Your numbers are stored as text, and the plus sign was hiding it.

Do not "fix" it by rewriting the total as a long chain of plus signs. Microsoft's SUM reference lists why that is fragile: a chain of + references breaks with #VALUE! the moment one cell holds genuine non-numeric text, it does not pick up rows you insert, and deleting a row leaves a #REF! error behind. Convert the data instead, and let SUM do its job.

Is calculation set to manual?

If the numbers are real (test 1 returned TRUE) but the total does not change when you edit a value, Excel has probably stopped recalculating. Press F9. If the total jumps to the right figure, you are in Manual mode.

This setting travels. Manual mode can be switched on by a colleague working with a large, slow workbook, or inherited from the first workbook you opened in a session. Microsoft's page on changing formula recalculation carries the important detail: in the Excel desktop apps, changing the calculation option affects all open workbooks, not just the one in front of you.

To switch it back:

Excel for Windows: Formulas tab, Calculation group, Calculation Options, Automatic. The same setting lives under File, Options, Formulas, Workbook Calculation.
Excel for Mac: Excel menu, Preferences, Calculation, then choose Automatic.
Excel for the web: Formulas tab, Calculation Options, Automatic. Here the change applies only to the current workbook.

The keyboard shortcuts are worth knowing while you diagnose. Per the same Microsoft page, F9 recalculates changed formulas in all open workbooks, Shift+F9 does the active sheet only, and Ctrl+Alt+F9 recalculates every formula whether it changed or not. If F9 does nothing but Ctrl+Alt+F9 fixes the total, you have a stale dependency rather than a data problem, and a full recalculation is the fix.

Why does the total ignore rows you just added?

When the total is too small rather than zero, check the range before the data. Click the total cell. Excel draws a coloured border around the range the formula reads. If that border ends above your newest rows, the formula is not broken. It is just not looking there.

This happens because SUM only adjusts its range automatically when you insert rows inside it. Microsoft's SUM reference says as much: the function updates for inserted rows as long as the insertion is not outside the range. Add a row directly under the last one, below the range's final cell, and SUM ignores it. Paste a new month of data under last month's and the total stays at last month's figure.

Three repairs, from quickest to most durable:

Drag the range. Double-click the total, drag the corner of the coloured border to the last row, press Enter.
Leave a buffer. Keep the total a row or two below the data with a blank row inside the range, and always insert new rows above that blank one. It works, and it relies on everyone remembering.
Use a table. Select the data and press Ctrl+T. Tables grow when you type directly under them, and formulas that refer to a table column follow it. Excel's table Total Row gives you the sum for free.

What about spaces you can't see?

This is the cause that survives every other fix. Test 1 says the value is text. You try Convert to Number and nothing happens. VALUE returns #VALUE!. The culprit is usually a character you cannot see, and most often it is the non-breaking space.

Web pages use the non-breaking space to keep text together, and it comes along when you copy a table out of a browser or a PDF. Microsoft's TRIM function reference is explicit about the trap: TRIM was designed to remove the ordinary space character, value 32, and by itself it does not remove the non-breaking space, value 160. So the usual advice to "just use TRIM" leaves the problem exactly where it was.

Confirm it with two formulas:

=LEN(A2)
=CODE(RIGHT(A2))

If LEN reports more characters than you can count on screen, something invisible is in there. If CODE on the last character returns 160, it is a non-breaking space. Swap RIGHT for LEFT to check the front of the value.

The repair strips the character and converts in one step:

=VALUE(SUBSTITUTE(A2,CHAR(160),""))

Fill it down a helper column, then paste the results back as values, as in the text repair above. For a one-off fix without formulas, open Find and Replace (Ctrl+H), click in Find what, and on a Windows keyboard with a number pad hold Alt and type 0160 on the keypad. Leave Replace with empty and choose Replace All.

Occasionally the stray character is something else: a tab, a line break, or a currency symbol from another locale. The same CODE test tells you which number to put inside CHAR(). Trailing and doubled ordinary spaces, and inconsistent headers, are covered in the messy spreadsheet checklist, which is worth running on any file that arrived from somewhere else.

Is the total counting itself?

The last test covers the case where the numbers are fine and the formula still misbehaves. If the total cell sits inside the range it sums, for example a total in B20 that reads =SUM(B2:B20), Excel cannot finish the calculation, because the answer depends on itself.

Microsoft's guide to removing or allowing a circular reference describes the symptoms. The status bar shows Circular References, sometimes with a cell address. Excel shows a warning the first time it finds one in a session, and after you close that warning the cell may display 0 or its last calculated value. Later circular references might not trigger the warning again, which is how a zero total slips through unnoticed.

To find the cell, go to Formulas, Error Checking, Circular References, and pick each address listed. The fix is almost always to move the total out of its own range, or to end the range one row higher. If the loop runs through other cells rather than the total itself, Trace Precedents on the same tab shows you the chain. The page notes that Excel for the web has more limited tracing tools, so open the file in the desktop app if the command is missing.

Where SuperSheet fits

Text-stored numbers are the most common cause, and also the one that hides best, because a column can be almost entirely real numbers with a few text values mixed in and still look perfect. Our spreadsheet cleaner exists for that case. Drop the file on the cleaner and it names every column holding numbers stored as text before it changes anything, with a count of affected cells and stray spaces alongside. You approve the repairs you want. Parsing and cleaning run in your browser, and the result downloads as a normal .xlsx with the totals rebuilt from live SUM formulas.

It does not rewrite your formulas or move a total that sits inside its own range. Tests 2, 3 and 5 stay in your hands, and the steps above take a minute each.

FAQ

Why does Excel SUM show 0?

Most often because the values in the range are stored as text, and SUM skips text. Check with =ISNUMBER(A2): FALSE confirms it. If the values are real numbers, press F9 to rule out Manual calculation, then look at the status bar for a Circular References message.

How do I convert a whole column of text numbers at once?

Select the column, choose Data, Text to Columns, and press Finish on the first screen without changing any settings. Excel re-reads every cell and turns numeric-looking text into numbers. If the column also contains non-breaking spaces, that will not be enough; use =VALUE(SUBSTITUTE(A2,CHAR(160),"")) in a helper column and paste the results back as values.

Why doesn't the status bar show a sum?

The Sum on the status bar only covers cells Excel treats as numbers. Select a column of text-stored numbers and you will see a Count and no Sum, which is itself a quick test for cause 1. If it is missing even for real numbers, right-click the status bar and make sure Sum is ticked.

Does Google Sheets SUM ignore text numbers too?

Yes. SUM in Google Sheets also skips text in a range, while the plus operator converts text that looks like a number, so the same =A1+A2 versus SUM mismatch appears there. ISNUMBER works the same way as a test. Changing the number format does not convert the values in Sheets either; use VALUE in a helper column and paste the results back as values only.