Yesterday night, while working on my Excel worksheet, when I used the SUM formula in a cell & selected the columns, then it returned 0. At that time, I thought that it is because of the double quotes in a cell. So, to resolve the Excel sum formula not working returns 0 issue, I formatted the cells but it was still returning 0. Luckily, I have fixed this problem by using some effective ways.
In this optimized post, you will find those solutions that helped me troubleshoot the Excel sum time returns 0 issue.
To fix corrupted Excel files, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
Why Is My Sum Formula Returning 0 in Excel?
Well, we cannot blame a single reason why does Excel sum formula not working returns 0. But when you format of the cell is set erroneously then it mostly triggers this issue.
Moreover, when the numbers are formatted as the text values in a cell then it can also cause this problem.
As you have known the major factors for Excel 2016 sum formula not working returns 0 now, let’s proceed to fix it.
How to Solve Excel Sum Formula Not Working Returns 0 Issue?
By following the below workable solutions, you can easily stop the sum formula returning 0 in MS Excel.
Case 1: When Number Kept as a Text
As I have already mentioned above, when the numbers are formatted as the text values in a cell, it can trigger this problem.
Therefore, here I will show how you can tackle this issue by trying 4 easy ways:
Way 1: Using Convert to Number Command
The very first method I would like to suggest is to use the handy command known as Convert to Number. This command will eventually assist you to convert your text values to numbers.
Here are the steps to do this:
- Initially, select cells.
- Next, click on an error icon.
- Navigate to Context menu & choose Convert to Number.
Now, you can see the values are converted to numbers successfully & the SUM function is working properly.
Also Read: Fix Excel Formula Not Auto Calculating
Way 2: Try Paste Special in Excel
Paste Special is another efficient command that helps to convert Text to Numbers. However, converting to numbers using this command is a tricky way but still, it is helpful in some cases.
Here are the steps to do this:
- Firstly, copy any of the blank cells.
- Then, select cells & right-click on them.
- Navigate to Context menu & choose Paste Special.
- After that, Paste Special dialog box will appear, from a Paste section, you have to mark ALL. Also, choose Add from an Operation section.
- Lastly, press OK.
Way 3: Use Text to Columns Wizard
You can even use Text to Columns Wizard to do this. Follow the below steps:
- Select cells C5:C9.
- After that, navigate to Data menu. Then, click Text to Columns.
- Once the dialog box opens, you will see 3 steps.
- In the 1st step, simply mark Delimited.
- Then, press Next.
- Next, mark Tab >> press Next.
- Now, mark the General in a last step >> press Finish option.
Then you’ll get the appropriate output from a SUM formula.
For more solutions to convert Text to Number, Click Here.
Case 2: When Calculation Mode Is in Manual Mode
In most of the earlier versions, it has been seen that when the calculation mode is in Manual mode, it triggers Excel sum function not working returning zero. But it does not happen in the newest version like Excel 365.
Solution:
If you are using the previous version of Excel, follow the below steps and set the calculation mode to Automatic
- Open the Excel, then find & click on Formulas
- After this, click on Calculation Options then Automatic.
You are done.
Also Read: Fix Excel Formula Is Too Long Error
Case 3: When Cells Hold Non-Numeric Characters
If the Excel cells contain non-numeric characters (commas, brackets, space, etc) along with numbers, then it can also result in Excel sum formula not working returns 0 when using SUM.
In that case, try to remove the non-numeric characters from the cells to get rid of the issue.
Solution:
Removing the non-numeric characters manually isn’t viable for the large dataset. Therefore, using Find & Replace feature in Excel is the best option.
Here Are the Steps:
- Select data range from C5:C9.
- Then, press the Ctrl + H keys together to open Find & Replace
- After this, type in a comma (,) in a Find what box & keep the Replace with field empty.
- Lastly, press the Replace All.
Now, this tool will remove all the commas & solve the problem.
Note: You can follow the same steps to remove other non-numeric characters. Only you need to replace comma (,) with another non-numeric character.
How to Repair & Recover Corrupted Excel Files?
If somehow your Excel document gets corrupted, it is suggested to try the most reliable Excel Repair & Recovery Tool.
This program allows for fixing corrupted or damaged Excel files and also recovering its components such as charts, tables, images, formulas, cell comments, and more.
So, download this software on your PC & follow the below user guide of this tool to repair the corrupt Excel:
Related FAQs:
It might possible that you have unintentionally activated a Show Formulas mode in an Excel worksheet that’s why an Excel not giving you a Sum.
The 3 ways to use the Sum Function in MS Excel are:
To get Excel to stop returning 0, make sure that cells are formatted as numbers, not text. Also, ensure to use the ISNUMBER function to tackle the situation. Why Is Excel Not Giving Me A Sum?
What Are 3 Ways to Use the SUM Function in Excel?
How Do I Get Excel to Stop Returning 0?
Time to Sum Up
Well, I have tried my best to cover all the feasible methods for you to deal with the Excel sum formula not working returns 0 problems.
Though you are suggested to try them one after another until your issue gets resolved.
Hopefully, the blog explains the problem well & helped you to get rid of it using the above-mentioned ways. For more queries or doubts, comment to us on Facebook and Twitter for support.
Good Luck…!
Priyanka Sahu
Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.