Mashhura Jahan
AutoSum helps to sum a range automatically. If you select a range that you want to sum with a blank cell under it or on the right side of the range and then select AutoSum then it will return the summation of the selected range in the blank cell. But sometimes you may face a situation where AutoSum is not working and returns 0 in Excel. The main objective of this article is to explain the reasons behind this problem and show how you can solve them.
Download Practice Workbook
You can download the practice workbook from here.
3 Reasons with Solutions When AutoSum Is Not Working in Excel and Returns 0
To explain this article, I have taken the following dataset. It contains the Subject name and Marks. Here, I used AutoSum to get the Total. But in the following picture, you can see that the AutoSum is returning 0. I will explain 3 different reasons with solutions why Autosum is not working and returns 0 in Excel.
Reason-01: Numbers Stored as Text in Excel
Sometimes, numbers are stored as text. This is the first reason why Excel AutoSum is not working and returns 0. In the following image, you can see that there is an apostrophe (‘) before the number and for this reason, the numbers are stored as text here. And as the numbers are stored as text AutoSum returns 0 here. Now, I will show you 5 different solutions to this problem.
Solution-01: Use Error Warning to Convert to Number
Here, the dataset shows an error warning because the numbers are stored as text here. In this first solution, I will use the error warning to convert the texts to numbers. Let’s see the steps.
Steps:
- First, select the cells where the numbers are stored as text. Here, I selected the cell range C5:C9.
- Then, click on the Error Warning.
- Next, select Convert to Number.
- After that, you will see that the numbers are stored as numbers. Also, the AutoSum is working properly and returns the correct result.
Read More: How to Use AutoSum In Excel (4 Easy Ways)
Solution-02: Apply Mathematical Operations
There are different types of mathematical operations. Here, I will apply the Multiplication operation to convert the numbers that are stored as text to numbers. Let’s see how you can do it.
Steps:
- In the beginning, make a column where you will convert the numbers.
- Afterward, select the first cell of that column. Here, I selected cell D5.
- Next, in cell D5 write the following formula.
=C5*1
- Then, press Enter to convert it to a number.
- Further, drag the Fill Handle down to copy the formula to the other cells.
- After that, you will see that you have copied the formula to the other cells and got your desired output.
- Finally, you will see that the AutoSum is working and returns the correct result for this range.
Solution-03: Employ Paste Special Option
The Paste Special option in Excel allows you to select how you want to display the content you copied. In this solution, I will use the Paste Special option to convert the numbers. Let me show you the steps.
Steps:
- Firstly, select a blank cell from the worksheet. Here. I selected cell B12.
- Secondly, copy the cell by pressing Ctrl + C on your keyboard.
- Thirdly, select the cells where you want to convert the numbers.
- After that, Right-Click on the selected cells.
- Then, select Paste Special.
- Next, the Paste Special dialog box will appear.
- Select Values from Paste.
- Afterward, select Add.
- Then, select OK.
- Finally, you will see that the numbers are converted and the AutoSum is working properly.
Solution-04: Apply VALUE Function
The VALUE function is one of the text functions of Excel. It returns the value of a text. If the text matches a specific format then the function returns the value in that format. Here, I will use the VALUE function to convert the numbers that are stored as text to numbers. Let’s see the steps.
Steps:
- First, select the cell where AutoSum is not working and returns 0. Here, I selected cell C10.
- Then, in cell C10 write the VALUE function in the AutoSum like the following formula.
=SUM(VALUE(C5:C9))
- In the end, press Enter and you will see that the formula is working and returning the correct result.
Solution-05: Use Text to Columns Wizard
Here, I will use the Text to Columns Wizard to solve when Excel AutoSum is not working and returns 0. I will convert the text to numbers by using the Text to Columns Wizard. Let’s see how you can do it.
Steps:
- Firstly, select the cells where the numbers are stored as text.
- Secondly, go to the Data tab.
- Thirdly, select Text to Columns.
- After that, the Convert Text to Columns Wizard – Step 1 of 3 will appear.
- Make sure Delimited is selected.
- Then, select Next.
- Next, the Convert Text to Columns Wizard – Step 2 of 3 will appear.
- Check Tab from Delimiters.
- Afterward, select Next.
- Further, the Convert Text to Columns Wizard – Step 3 of 3 will appear.
- Select General.
- Lastly, select Finish.
- Finally, you will see that you have converted the numbers and the AutoSum is working properly and returning the correct result.
Read More: How to Autosum Column in Excel (4 Easy Methods)
Reason-02: Non-Numeric Characters in Number
The second reason when Excel AutoSum is not working and returns 0 is entering non-numeric characters in numbers. If you enter a non-numeric character in a number on purpose or by mistaken then the number is converted to text. In the following image, you can see that I have entered the Number Sign (#) in the numbers. As a result, the AutoSum is not working here and returns 0. Now, I will show you how you can solve this problem.
Solution: Employ Find and Replace Feature
The Find and Replace feature will help you to find something in an Excel sheet and replace it with whatever you want. Here, I will use this feature to remove the non-numeric character from the numbers. Let’s see the steps.
Steps:
- In the beginning, select the cells where you have entered the non-numeric character with numbers.
- Then, press Ctrl + H from your keyboard, and the Find and Replace dialog box will appear.
- Next, write the non-numeric character you want to remove in the Find what section. Here, I wrote the Number Sign (#).
- And then, leave the Replace with section blank.
- Afterward, select Replace All.
- Next, another dialog box will appear showing how many replacements you have done.
- Select OK.
- Here, you will see that the non-numeric characters are removed and the AutoSum is working properly.
Reason-03: Circular References in AutoSum
Circular References in Excel occur when a formula refers to its own cell. Circular References is another reason why Excel AutoSum is not working and returns 0. In the following picture, you can see that the numbers have no errors in them. But yet the AutoSum is returning 0. Circular References can be the reason here.
Let’s see how you can check for Circular references.
- To begin with, go to the Formulas tab.
- Then, select the drop-down option for Error Checking.
- Next, select Circular References.
- Consequently, you will be able to see the cells that are causing the Circular References. Here, cell C10 is causing the error.
Solution: Remove Circular References
To solve the Circular References error you will have to remove the cells that are causing the error from the formula and then you will be good to go. Let’s see the steps.
Steps:
- Firstly, select the cell where the AutoSum is not working and returns 0.
- Secondly, remove the cell that is causing the error. Here, I removed cell C10 and got the following formula.
=SUM(C5:C9)
- After that, press Enter and you will see that the AutoSum is working and returns the proper result.
Practice Section
Here, I have provided a practice sheet for you to practice why Excel AutoSum is not working and returns 0.
Conclusion
In this article, I tried to cover why Excel AutoSum is not working and returns 0 with some solutions. Here, I explained 3 reasons with solutions. I hope this article was clear to you. For more articles like this stay connected with ExcelDemy. Feel free to comment if you face any problems.
