SUM returns 0 — even though the cells have numbers in them

How to fix it

The cause is almost always that your "numbers" are stored as text, and the fix is to convert them back into real numbers. There are a few ways:

Text to Columns (fastest for a whole column). Select the column. Go to Data > Text to Columns. Click Finish immediately without changing any settings. Excel re-processes the column and converts text-numbers to real numbers. This is the most reliable approach for a large block of cells.

Paste Special — Multiply by 1. Type the number 1 in an empty cell and copy it. Select the problem column. Right-click > Paste Special > Multiply. Click OK. Multiplying by 1 forces Excel to treat each cell as a number, which converts the text in the process. Delete the helper cell afterward.

VALUE() formula. In a helper column, use =VALUE(A2) to convert one cell at a time. Fill down the column, then copy and paste the helper column as values over the original, and delete the helper.

Once the cells hold real numbers, your SUM will update immediately — the total should jump to the correct value as soon as the conversion is complete.

How to confirm this is your problem

There are two quick checks:

The alignment test. Select the column of values and look at which side of the cell they sit on. Real numbers lean to the right by default. Text leans to the left. If a column of "numbers" is all left-aligned, they're probably stored as text.

The green triangle test. Click one of the suspicious cells. If you see a tiny green triangle in the top-left corner of the cell, Excel is already warning you — it thinks the value is a number stored as text. Click the yellow diamond that appears next to the cell to see the message.

You can also run a quick check: in an empty cell nearby, type =ISNUMBER(A2) (replacing A2 with one of your cells). If it says FALSE, that cell is text, not a number.

Why it happens

Excel draws a hard line between numbers and text that looks like numbers. When a cell stores a number, Excel can add it up. When a cell stores the word "150" — characters that happen to look like a number but are actually treated like letters — Excel skips it silently. SUM doesn't add text; it just moves on. The result is zero, or whatever real numbers were mixed in. And Excel gives you no error indicator: the cells don't show #VALUE! or any other code, and the SUM cell itself looks perfectly normal — which is what makes this one of the sneakiest problems in Excel.

This happens most often when data enters Excel from somewhere else. A download from accounting software, an export from a database, a copy-paste from a web page — these external sources frequently encode numbers in a way that Excel reads as text. The values look identical on screen, but the storage underneath is different.

After the fix

You won't need to re-enter the formula — the SUM recalculates on its own. If the total still looks wrong after converting, check whether the SUM range is capturing all the rows you intend. A range that was set when the column was short won't automatically expand if you added rows later.

This same text-vs-number problem causes VLOOKUP to return #N/A, causes AVERAGE to report a wrong average, and causes sorting to put numbers in the wrong order. Fixing the column storage fixes all of them at once.