Excel SUM Showing 0? Causes and Fixes

When Excel SUM is showing 0, the formula is usually fine. The problem is the data: the cells look like numbers but are stored as text, and SUM quietly skips text. This guide walks through the six causes of a zero total in the order you should check them, with a quick test and a fix for each one.

Why is SUM showing 0? A quick checklist#

SUM adds numbers only. It ignores text, blank cells and TRUE/FALSE values in a range, and it doesn't warn you. So a 0 means SUM found no real numbers in the range, or something stopped it calculating.

Check these causes in order:

Here is the classic example. The Units column was imported from another system:

=SUM(B2:B6) in B7 returns 0, but the real total is 810.

Cause 1: numbers stored as text#

Numbers stored as text are the most common reason SUM shows 0. They usually come from CSV files, web pages, other software, or cells formatted as Text before the numbers were typed.

To spot them:

• The numbers sit on the left of the cell. Real numbers align right by default.
• A small green triangle appears in the top-left corner of each cell.
• Select the cells and look at the status bar. You see a Count but no Sum or Average.

To fix them, select the cells, click the yellow warning icon that appears, and choose Convert to Number. For a whole column, Data > Text to Columns > Finish also works: Excel re-enters every value as a number.

If you can't change the source data, convert inside the formula instead:

=SUMPRODUCT(VALUE(B2:B6))

VALUE turns each text number into a real number and SUMPRODUCT adds them, so the result is 810. The VALUE function guide covers which text it can convert. The full guide to numbers stored as text has more ways to convert them.

Cause 2: hidden spaces and odd characters#

Sometimes Convert to Number does nothing and VALUE returns #VALUE!. That means the text holds a character that isn't a digit, usually a space at the start or end, or a non-breaking space copied from a web page. A non-breaking space looks like a normal space but is a different character, CHAR(160), and TRIM doesn't remove it.

Clean and convert in one formula, for example in a helper column:

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

SUBSTITUTE removes the non-breaking spaces, TRIM removes ordinary extra spaces, and VALUE makes the result a number. Fill it down, then SUM the helper column.

Cause 3: formulas that return text#

Text functions always return text, even when the result looks like a number. If C2 holds the code "INV-0450" and you use =RIGHT(C2,3) to get 450, the result is the text "450". A SUM over those cells gives 0.

The same happens with TEXT, MID, LEFT, joining with &, and numbers typed inside quotes like ="50". Wrap the formula in VALUE so it returns a real number:

=VALUE(RIGHT(C2,3))

Now SUM can add the results. A quick test: =ISNUMBER(D2) returns FALSE for any cell SUM will skip.

Cause 4: a circular reference#

If the SUM range includes the cell the formula is in, Excel can't finish the calculation. This is a circular reference. For example, =SUM(B2:B7) typed in B7 refers to itself. Excel shows a warning, writes "Circular References" in the status bar, and the cell often shows 0.

Fix it by making the range stop above the total: =SUM(B2:B6). To find the cell, go to Formulas > Error Checking > Circular References. The guide to finding and fixing circular references explains more.

Cause 5: calculation is set to Manual#

When calculation is Manual, formulas don't update when the data changes. If you typed the SUM before pasting the numbers in, it can stay at 0 until you recalculate.

Press F9 to recalculate (on a Mac laptop you may need Fn+F9), or fix it for good with Formulas > Calculation Options > Automatic. Manual mode can be switched on by another workbook you opened in the same session, so it's worth checking. See why Excel formulas stop updating for the details.

Cause 6: the wrong range or a misleading format#

Sometimes SUM is right and you're looking at the wrong thing:

• The range points at empty cells. After rows are moved or a formula is copied, =SUM(B2:B6) may now read =SUM(C2:C6). Click the cell and check the coloured range outline. The article on a SUM that gives the wrong total covers range problems.
• The format hides the decimals. If the values are 0.1, 0.2 and 0.1, the total is 0.4. With a number format of 0 (no decimals), it displays as 0. Click Increase Decimal on the Home tab to see the real value.
• A custom format hides the number. A format such as ;;; shows nothing at all. Check the formula bar, which always shows the real content.

Practise it#

Practise debugging a broken SUM in a live spreadsheet: you'll find text numbers and bad ranges and fix the total yourself. Then practise converting text to numbers with VALUE, and keep the SUM function guide open as a reference. Or start with the exercise on this page.