Why Excel SUM shows 0 (or doesn't add up)

1The numbers are stored as text

Numbers pasted from a website, a PDF or a bank or accounting export often come in as text. They look like numbers, but SUM ignores them, so the total is 0 or too small.

How to recognise it

• The numbers line up on the left of the cell instead of the right.
• A small green triangle shows in the top-left corner.
• Select them and the status bar shows Count but no Sum.
• =COUNT(B2:B10) returns 0, or fewer than the number of values.

Fix

Pick whichever is easiest:

1. Select the cells, click the warning icon next to the first one, and choose Convert to Number.
2. Select one column and click Data > Text to Columns > Finish.
3. Type 1 in an empty cell and copy it. Select the numbers, then Home > Paste > Paste Special, choose Multiply and click OK.

Or leave the data alone and convert inside the formula. This works in every version:

=SUMPRODUCT(--B2:B10)

In Excel 2021 and Microsoft 365 you can also write =SUM(--B2:B10). The two minus signs turn each text number into a real number. If a cell contains text that isn't a number, such as "n/a", you get #VALUE!.

If Convert to Number doesn't appear or doesn't work, the cells may contain hidden characters such as non-breaking spaces or a currency word. Clean them with =--TRIM(SUBSTITUTE(B2,CHAR(160)," ")) in a helper column.

Prevent it. Format input columns as Number before you paste, and use Paste Special > Values or Data > From Text/CSV for exports.

2Calculation is set to Manual

How to recognise it

You change a number but the total stays the same. The status bar may show Calculate.

Fix

Go to Formulas > Calculation Options and choose Automatic. To recalculate once without changing the setting, press F9.

The calculation mode applies to the whole Excel session. If the first workbook you open was saved in Manual mode, every workbook you open after it is Manual too.

3The total cell is formatted as Text

How to recognise it

Instead of a number, the cell shows the formula itself, such as =SUM(B2:B10).

Fix

Change the cell's format to General on the Home tab, then click into the cell, press F2 and then Enter. There's more on this in Excel formula shows as text or doesn't update.

4The SUM includes its own cell (circular reference)

If the total in B11 is =SUM(B2:B11), it tries to add itself. Excel warns you about a circular reference and usually shows 0.

How to recognise it

The status bar shows Circular References followed by a cell address.

Fix

Go to Formulas > Error Checking > Circular References and click the cell listed there. Change the range so it stops above the total:

=SUM(B2:B10)

5Hidden decimals make the total look wrong

Cells can show 0.33 while holding 0.3333333. Three of them show 0.33 each but total 1.00, so the total doesn't match the numbers on screen. This often shows up in invoices and percentages.

How to recognise it

Click a cell and compare the formula bar with what the cell shows, or click Increase Decimal on the Home tab.

Fix

Round each value where it's calculated, so the total adds up what you see:

=ROUND(B2*C2,2)

Or round the total: =ROUND(SUM(D2:D10),2).

Excel also has File > Options > Advanced > Set precision as displayed. It permanently cuts the stored values down to what's displayed for the whole workbook, so ROUND is the safer choice.

6Filtered or hidden rows are still being added

How to recognise it

You filtered the list but the total didn't change. SUM adds hidden rows too.

Fix

Use SUBTOTAL, which skips rows hidden by a filter (109 also skips rows you hid by hand):

=SUBTOTAL(109,B2:B100)

AutoSum puts in SUBTOTAL for you when you use it right below a filtered list.