Excel SUM Formula Returns 0? Causes and Fixes

Formulas & functions · Updated November 19, 2026

If an Excel SUM formula returns 0, first check whether its referenced cells contain real numbers and whether the formula points to the intended range. SUM ignores text, so values that look numeric but are stored as text are a common cause; an incorrect range or a formula entered as text can also explain the result.

Why SUM can return zero

The SUM function adds numeric values in the cells or ranges you specify. If every cell in the referenced range is blank or contains text rather than numbers, the result is 0. This can happen even when the worksheet visibly contains entries such as 12, 8, and 5. Excel may be displaying those entries as text, especially if they came from a web page, a CSV file, another application, or a system that exports numbers with unexpected separators.

Start by checking the formula itself. With amounts in B2:B4 and a total in B5, the formula =SUM(B2:B4) returns 60 if B2, B3, and B4 contain the numeric values 10, 20, and 30. If the range is empty, points somewhere else, or contains only text values, the same formula can return 0. Excel does not infer the intended range from nearby data; it adds exactly what the formula references.

Also distinguish a displayed zero from a formula that has not calculated. A result may be stale when calculation is set to Manual, while a formula displayed literally in the cell often means Excel is treating the entry as text or showing formulas instead of results. Those symptoms call for different fixes.

How SUM treats numbers, text, and blanks

SUM adds numbers, including numeric values returned by many formulas, and skips blank cells and text in a referenced range. It does not automatically convert every numeric-looking text entry into a number. A cell containing the text value 25 is not necessarily equivalent to a cell containing the number 25, even if both appear identical on screen.

Formatting alone can be misleading. Applying the Number format to a text value does not reliably convert that value into a number; it may still be text. Likewise, a leading apostrophe can deliberately store an entry as text. Imported spaces, nonbreaking spaces, currency symbols, or a decimal separator from a different regional setting can also stop Excel recognizing an entry as numeric.

Blanks are different from text that looks blank. A truly empty cell contributes nothing. A formula that returns an empty string may display as blank and is not a numeric amount for SUM to add. If a range mixes numbers and text, SUM adds the actual numbers and ignores the text entries, which can produce a total that is lower than expected without returning an error.

Check whether the values are stored as text

Look for Excel’s green error indicator in the corner of a cell, which may offer a warning that a number is stored as text. The warning is not shown in every case, so use more than one check:

  • Select a suspect cell and inspect the formula bar. A leading apostrophe or unexpected spaces can reveal text entry.
  • Check alignment as a clue: numbers are commonly right-aligned by default and text left-aligned, though custom formatting can change alignment.
  • Use the status bar as a quick check. Select several suspected values and see whether Excel reports a sum. If it reports a count but no sum, the selection may contain text rather than numeric values.
  • Compare the number of numeric cells with the number of nonempty cells. With suspected values in A2:A6, =COUNT(A2:A6) returns the number of numeric cells, while =COUNTA(A2:A6) counts nonempty cells. If the first result is 0 and the second is 5, none of those five cells is recognized as a number.

In the last check, the layout is values in A2:A6. If all five cells contain entries but all are text, COUNT returns 0 and COUNTA returns 5. If three cells contain numbers and two contain text, the corresponding results are 3 and 5. These checks do not change the source data.

Convert text numbers into real numbers

When Excel flags numbers stored as text, select the affected cells, choose the warning icon, and select Convert to Number if that option appears. For a large imported column, Windows Excel for Microsoft 365 also offers a reliable conversion route: select the column, open Data > Text to Columns, leave the default delimited choice if appropriate, and select Finish. Check a few results before using the converted column in important calculations. The Text to Columns command is also available in Excel for Mac, although ribbon placement can vary.

A formula can convert a single text number when the text uses a format Excel recognizes. With a text number in A2, =VALUE(A2) returns its numeric value. If A2 contains the text 12, the result is the number 12. Fill the formula down beside the source data, then copy the converted results and use Paste Special > Values if you need to replace the originals. VALUE cannot reliably interpret every currency format, extra character, or regional number convention; clean or import the data with the correct separators if conversion fails.

For a quick one-time conversion, another option is to enter 1 in an empty cell, copy it, select the text-number cells, and use Paste Special > Multiply. This can coerce recognizable numeric text into numbers, but it changes the selected data. Keep a copy of the original column first, and do not use this approach on mixed data that you have not checked.

Verify the formula and its referenced range

Click the total cell and inspect the formula bar. Confirm that the formula begins with an equals sign and that the highlighted references cover the intended cells. A common mistake is summing the wrong column, an empty range, or a range that stops above newly added rows. With amounts in B2:B4 and the total in B5, =SUM(B2:B4) returns 60 when the three amounts are 10, 20, and 30. If the actual data is in C2:C4, the formula can validly return 0 because B2:B4 is empty.

Excel’s colored outlines help identify the referenced cells while you edit a formula. You can also select the formula and press Enter after correcting the range. If the total sits directly below a contiguous list, AutoSum can suggest a range: select the cell below the values, then choose Home > AutoSum or Formulas > AutoSum. Inspect the suggested outline before accepting it, particularly if there are blank rows or other numbers nearby.

When a formula appears in the cell instead of a result, check whether the cell is formatted as Text. Change it to General or Number, then re-enter the formula by pressing F2 and Enter on Windows. Also check Formulas > Show Formulas; if that option is enabled, Excel displays formulas across the sheet instead of their calculated results. These are display or entry issues, not evidence that SUM itself is adding incorrectly.

Check calculation settings and workbook state

Excel can be set to Manual calculation, which means formulas may not update automatically after their inputs change. On Windows, check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. Excel for Mac has calculation options on the Formulas tab as well, though the ribbon layout may differ. Recalculate after changing the setting and confirm that the source values are correct.

Manual calculation is more likely to leave an old result on screen than to make a correct formula intrinsically return zero. If a displayed zero persists, inspect the range and its values rather than assuming recalculation will fix it. A workbook can also contain formulas that depend on other cells, so check the upstream cells if the SUM range contains formulas that currently evaluate to zero or blank.

Understand filters, hidden rows, and alternative totals

SUM includes numeric values in hidden rows and rows excluded by a filter, as long as those cells are inside the referenced range. That behavior usually makes the total higher than the visible subtotal, not zero, but it can cause confusion when a filtered list is expected to show only visible records. For a filtered list with amounts in B2:B5, =SUBTOTAL(9,B2:B5) returns the sum of visible rows and ignores rows removed by the filter. If the values are 10, 20, 30, and 40 and the filter hides the row containing 20, the result is 80.

SUBTOTAL is useful when the requested answer is specifically a visible-row total; it is not a repair for text-formatted numbers. If every visible amount is stored as text, the subtotal can still be 0. First confirm that the data is numeric, then choose the function that matches the intended calculation. For a normal total that should include hidden rows, SUM remains the appropriate choice.

Prevent zero totals in imported data

For recurring reports, fix the import process rather than manually repairing each new file. Before importing, check whether the source uses commas or periods for decimals, whether it includes currency symbols, and whether values contain leading or trailing spaces. When opening a CSV or text file, use Excel’s import tools and check the preview and column data types before loading it. A column that looks correct in the preview can still need conversion, so verify a sample after import.

It also helps to keep raw imported data separate from cleaned data and totals. Convert a copy, record the expected number of numeric entries, and compare a sample against the original source. This makes it easier to identify whether the problem is the formula, the file format, or the underlying values. If totals are used in a report, label whether they include filtered or hidden rows so readers understand what the number represents.

Frequently Asked Questions

Why does SUM return 0 when the cells visibly contain numbers?
Those entries may be stored as text, or the formula may point to different cells than the ones you are viewing. Check the formula range and use COUNT on the suspected cells to see how many Excel recognizes as numeric.

Does changing a cell to Number format convert text into a number?
Not necessarily. Number formatting changes how recognized values are displayed; it does not always convert existing text. Use Convert to Number, Text to Columns, or a suitable conversion formula, then verify the result.

Why does SUM ignore some cells but not show an error?
SUM normally ignores text entries in a referenced range rather than reporting an error. If a range contains both numbers and numeric-looking text, it adds only the recognized numbers, so the total may be incomplete.

Will SUM include filtered or hidden rows?
Yes. SUM includes numeric cells in its range even when their rows are hidden or filtered out. Use SUBTOTAL when you need a total that responds to filtered rows.

Why does my formula show instead of calculating?
The cell may be formatted as Text, the formula may have been entered with a leading apostrophe, or Show Formulas may be enabled. Change the cell format if needed, re-enter the formula, and check the Show Formulas control on the Formulas tab.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.