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 was 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 returns 0.
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:
- Try 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.
Quick Navigation:
Part 1- Understanding SUM Formula in Excel
Part 2- Why Is My Sum Formula Returning 0 in Excel?
Part 3- How to Solve Excel Sum Returns 0 Issue?
- Case 1: When Number Kept as a Text
- Case 2: When Calculation Mode Is in Manual Mode
- Case 3: When Cells Hold Non-Numeric Characters
Part 4- How to Repair & Recover Corrupted Excel Files?
Understanding SUM Formula in Excel
The SUM function or formula is generally used to add up the range of numbers in a certain cell range. Below you can see its basic syntax:
=SUM(range)
In the above syntax, the “range” signifies the cells that you need to add together. When it functions properly, it is a quick and effective way to calculate totals. Nevertheless, when you get 0 as a result, it can be baffling.
Why Is My Sum Formula Returning 0 in Excel?
Well, you cannot blame a single reason why SUM in Excel is 0. But when your 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 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 getting 0 in MS Excel when using SUM.
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 Not Recognizing Date Format
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 the Excel sum not working.
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.
Yes, you can use the SUM formula with non-contiguous cells. To sum non-contiguous cells, you have to select the range, then hold a Ctrl key. After that, select the other ranges that you need to include.
The 3 ways to use the Sum Function in MS Excel are:
If your SUM formula shows #VALUE! error, it might be possible that there is an issue with the data types in your selected range. So, check the data in the cells & ensure they are in numbers.
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.
The SUM function allows you to select the range manually to calculate, whereas the AutoSum allows you to select the range automatically to sum, which can save your time.
There are various limitations to Excel’s SUM formula, but the major drawback is- tremendously large datasets might impact the performance. Why Is Excel Not Giving Me A Sum?
Can I Use the SUM Formula with Non-Contiguous Cells?
What Are 3 Ways to Use the SUM Function in Excel?
What Do I Do If My SUM Formula Shows #VALUE! Error?
How Do I Get Excel to Stop Returning 0?
What's The Difference Between SUM And AutoSum?
Are There Any Limitations to Excel's SUM Formula?
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. However, 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…!
