If your Excel SUM formula returns 0 when it should show a total, the cause is almost always that the cells contain text instead of 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; if that doesn’t work, use the VALUE function or re-enter the numbers.
Why SUM Returns 0: The Text Number Problem
Excel treats text and numbers differently. A cell that looks like a number (e.g., “123”) but is actually stored as text will be ignored by SUM. This is the #1 reason for a zero total.
How to check: select the cells and look at the Status Bar at the bottom of Excel. If it shows only “Count” and not “Sum,” then at least one cell is text. Also, text-formatted numbers align left by default, while real numbers align right.
How to Fix Text Numbers Quickly
Here are the fastest ways to convert text to numbers:
- Use the Error Warning: Select the cells, click the yellow diamond icon, and choose Convert to Number.
- Use Paste Special: Type
1in any empty cell, copy it, select your data, right-click > Paste Special > Multiply. This forces Excel to treat them as numbers. - Use Text to Columns: Select the column, go to Data > Text to Columns, click Finish (no need to change steps).
- Use the VALUE function: In a new column, enter
=VALUE(A1)and drag down.
Check for Hidden Characters or Spaces
Sometimes numbers have invisible spaces or non-breaking spaces (character code 160) imported from web pages. These make the cell text. Use the TRIM function to remove regular spaces, and CLEAN to remove non-printing characters.
With data in A1:A10, in B1 enter: =TRIM(CLEAN(A1)) and copy down. Then sum column B.
Formula Bar and Cell Formatting Misconceptions
Many users think that setting the cell format to “Number” fixes it. It does not. Changing format only changes how the value is displayed; it does not change text to a number. You must actually convert the data.
Also, check that your SUM range is correct. If you accidentally include a header or a cell that contains text, SUM will skip it but still total the numeric cells. If all cells are text, SUM returns 0.
Circular References and Calculation Mode
If your formula refers to itself (directly or indirectly), Excel shows 0 or a warning. Go to Formulas > Error Checking > Circular References to see if any exist.
Also, check Calculation Options: if set to Manual, formulas won’t update. Press F9 to recalculate, or go to Formulas > Calculation Options > Automatic.
When the Cell Contains a Formula That Returns Text
If your numbers are the result of a formula (e.g., =LEFT(A1,3)), that formula might return text. Wrap it in VALUE or multiply by 1. For example: =VALUE(LEFT(A1,3)).
Frequently Asked Questions
Why does my SUM formula show 0 even after converting to number?
If you still see 0, check for leading apostrophes (‘) in the formula bar. An apostrophe forces text. Use Find & Replace: select the range, press Ctrl+H, find ', replace with nothing. Also, check for spaces before the number.
How do I sum cells with numbers stored as text in different columns?
Use the SUMPRODUCT function with VALUE: =SUMPRODUCT(--(A1:A10)) or =SUMPRODUCT(VALUE(A1:A10)) (the latter requires Ctrl+Shift+Enter in older Excel). This forces each cell to be evaluated as a number.
My SUM works on some rows but not others. Why?
That means some cells are real numbers and some are text. Convert the text ones using the methods above. Check the Status Bar for “Sum” to see which rows are being counted.
Does Excel for Mac have the same issue?
Yes, the same text-vs-number issue exists on Mac. The conversion options are similar: select cells, go to the Data tab, choose Text to Columns, or use the error warning (which appears as a small triangle).
Can I prevent this from happening?
Yes. Before entering data, format the cells as Number or General. When importing data, use the Import Wizard and set each column’s data type to “General” or “Number”. Avoid copying numbers from web pages directly.
Final Checks and a Simple Test
To confirm the fix, type a new number in an empty cell and sum it with your range. If the total updates, you’re good. If not, check for a hidden filter that might be hiding rows, or ensure the sheet isn’t protected.
Remember: Excel’s SUM function ignores text, logical values (TRUE/FALSE), and empty cells. So a zero result always points to the data being text, not a broken formula.
Related
- Excel Power Query: Merge Rows from Columns or Tables
- Excel Power Query Merge Two Queries: Step-by-Step
- Excel Power Query: Merge 3 Tables
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Protect an Excel Sheet Without Locking EverythingTo protect an Excel sheet in Microsoft 365 for Windows, unlock any cells people should be able to…
- Excel Shortcut to Select an Entire ColumnIn Excel for Microsoft 365 on Windows, press Ctrl+Spacebar to select the entire worksheet column containing the active…
- How to Insert a Separation Line in Word: 3 Easy WaysTo insert a separation line in Word, type three hyphens on a blank paragraph and press Enter; Word…
- Excel Workbook Frozen? Causes, Checks, and FixesIf Excel seems frozen, first wait briefly and avoid clicking repeatedly; if it responds, save with Ctrl+S and…
- Excel Cell Merge Shortcut Keys and How to Use ThemIn Excel for Microsoft 365 on Windows, select the cells and press Alt+H, M, then C to use…
- Excel Material List Template: Plan, Price, and TrackAn Excel material list template is a worksheet for recording the items a project needs, how much to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.