Excel Sum Returning Zero? Fix It Fast

Errors & fixes · Updated November 22, 2026

If your Excel SUM formula returns zero instead of the expected total, the most likely cause is that the cells contain text that looks like numbers, or the cells are formatted as text. To fix it, select the cells, click the warning icon that appears, and choose “Convert to Number,” or use the VALUE function in a helper column.

Why Excel SUM Returns Zero

Excel treats numbers stored as text differently from true numbers. When you type an apostrophe before a number (like '123), import data from a CSV, or copy from a web page, Excel often stores the value as text. SUM ignores text values, so the result is zero. Another common cause is that the cells are formatted as Text before you enter the numbers—Excel then stores them as text even if they look numeric.

To check if a cell contains text, select it and look at the Number Format box on the Home tab. If it says “Text,” that’s the problem. Also, if the cell has a small green triangle in the corner, Excel is warning you that the number is stored as text.

How to Fix Text-Formatted Numbers

Here are the fastest ways to convert text to numbers:

  • Use the warning icon: Select the range, click the yellow diamond with an exclamation mark, and choose “Convert to Number.”
  • Use Paste Special: Type 1 in an empty cell, copy it, select your data, right-click > Paste Special > Multiply. This forces Excel to treat the text as numbers.
  • Use the VALUE function: In a helper column, enter =VALUE(A1) (assuming your data is in A1), then copy down. Replace the original data with these values.
  • Use Text to Columns: Select the column, go to Data > Text to Columns > Finish. This often converts text to numbers in one step.

After converting, your SUM should work correctly. For example, if you have text numbers in A1:A5, using =SUM(A1:A5) will return the correct total after conversion.

Other Reasons SUM Returns Zero

Sometimes the issue isn’t text formatting. Check these possibilities:

  • Hidden rows or filtered data: SUM ignores hidden rows when the data is filtered. Use =SUBTOTAL(109,A1:A10) to sum only visible cells.
  • Circular references: If your SUM formula is in a cell that is also in the range being summed, Excel returns 0. Check for a circular reference warning.
  • Cells contain spaces or non-printing characters: Use =TRIM(A1) to remove extra spaces, or =CLEAN(A1) to remove non-printing characters.
  • Formulas in the range return text: If a formula returns a text string like “10” instead of a number, SUM ignores it. Wrap the formula in =VALUE().

How to Prevent This in the Future

To avoid the text-number problem, follow these practices:

  • Format cells as Number before entering data: Select the range, go to Home > Number > Number.
  • Don’t use apostrophes: If you need to enter a number as text (like a ZIP code), use the Text format deliberately, but remember that SUM won’t include it.
  • Import data correctly: When importing from CSV, use Data > From Text/CSV and choose the correct data types in the import wizard.
  • Use the SUM function with arrays: If you have a range with mixed text and numbers, you can use =SUM(VALUE(A1:A10)) entered as an array formula (Ctrl+Shift+Enter on older Excel, or just Enter in Excel 365).

Using SUMIF and SUMIFS with Text Numbers

If you have text numbers and need to sum conditionally, you might run into the same issue. For example, =SUMIF(A1:A10,">0") will ignore text numbers. To include them, you need to convert them first. A common workaround is to use =SUMPRODUCT(--(A1:A10<>"")*(VALUE(A1:A10))) but this can be complex. Better to convert the data once, then use SUMIF normally.

Excel vs. Google Sheets: Same Problem, Different Fix

In Google Sheets, the same issue occurs, but the fix is slightly different. Google Sheets often auto-detects text numbers and converts them, but if not, you can use =VALUE(A1) or the “Convert to number” option in the error menu. Also, Google Sheets has a TO_PURE_NUMBER function that works similarly. In Excel for Mac, the steps are the same as Windows, but the keyboard shortcut for Paste Special is Cmd+Ctrl+V instead of Ctrl+Alt+V.

Frequently Asked Questions

Why does my SUM formula show 0 even though the cells have numbers?

Most likely the cells are formatted as text. Check the Number Format and look for green triangles. Use the Convert to Number option or Paste Special > Multiply to fix it.

Can I make SUM ignore text and still add numbers?

Yes, SUM already ignores text. If you want to include text numbers in the sum, you must convert them to real numbers first. There’s no built-in function that adds text numbers directly.

Why does SUM return 0 when I use filtered data?

SUM ignores hidden rows when the data is filtered. Use =SUBTOTAL(109, range) to sum only visible cells.

What if I have a lot of cells with text numbers?

Use the Paste Special > Multiply method: type 1 in a cell, copy it, select your data, right-click > Paste Special > Multiply. This converts all selected text numbers to real numbers in one go.

Does Excel have a function to convert text to number?

Yes, the VALUE function converts a text string that looks like a number to a number. For example, =VALUE("123") returns 123. You can also use NUMBERVALUE for more control.

Why does my SUM return 0 when the cells contain formulas?

If the formulas return text, SUM will ignore them. Wrap each formula in =VALUE() or ensure the formula returns a number. For example, if your formula is =A1&"", it returns text, so use =VALUE(A1) instead.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.