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
1in 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
- Excel Sales Tracking Template by Customer: How to Build One
- Excel Sales Tracking Template by Product: How to Build One
- Excel Reverse Mortgage Calculator Spreadsheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Page Breaks in Excel Won’t Go Away? How to Fix ThemIf page-break lines in Excel won’t go away, switch to View > Normal to leave Page Break Preview,…
- Outlook Best Match Not Working? Fix Search ResultsIf Outlook Best Match is not working, first check the search scope and try the same query in…
- Excel #NUM! Error: Causes, Meaning, and How to Fix ItExcel’s #NUM! error means a formula has an invalid numeric value or cannot complete a calculation with the…
- Excel Column Height: AutoFit Rows and Fix Wrapped TextExcel does not have a separate column-height setting: columns have width, while rows have height. To make text…
- Excel Won’t Let You Delete a Row? Try These FixesTo delete a worksheet row in Excel for Microsoft 365 on Windows, select its numbered heading, then choose…
- SUBSTITUTE Formula Not Working in Excel? Fix ItIf Excel’s SUBSTITUTE formula is not working, first check that the text to change and the text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.