# [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

While you are using the SUM function, you might get that it’s not working properly and returning zero if you don’t know the reasons. So in this article, I am going to show the most common reasons and 3 useful solutions to solve the issue if the SUM formula is not working and returns 0 in Excel.

## 3 Fixes: Excel Sum Formula Is Not Working and Returns 0

To explore the solutions, we’ll use the following dataset that represents some ordered Smartphones and their Quantity. ### 1. Number Stored As Text

Have a look that I have used the SUM function here to find the total quantity but it returned zero. Why is it happening? The reason is that I stored the numbers as text values. That’s why there are green triangular icons in every cell. So the SUM formula recognized no numbers and that’s why returned zero. #### Solution 1: Use Convert to Number

First, I’ll use the Convert to Number command to convert the text values into numbers. It is one of the easiest ways.

Steps:

• Select the cells.
• Then click on the error icon.
• Later, select Convert to Number from the Context menu. Now see, we got the values as numbers and the SUM formula worked properly. #### Solution 2: Apply Text to Columns Wizard

Another useful solution is to use the Text to Columns Wizard.

Steps:

• Select the cells C5:C9.
• Then click as follows: Data ➤ Data Tools ➤ Text to Columns.
• Soon after a dialog box with 3 steps will open up. • In the first step, mark Delimited.
• Later, press Next. • After that mark Tab and press Next. • Mark General in the last step.
• Finally, just press Finish. Then you will get the proper output from the SUM formula as it should be. #### Solution 3: Apply Paste Special Command

Now let’s use a tricky way- Paste Special to convert Text into Number. It may be helpful in some particular cases.

Steps:

• First, copy any blank cell. • Next, select the cells and right-click your mouse.
• Then select Paste Special from the Context menu. • After appearing the Paste Special dialog box, mark All from the Paste section and Add from the Operation section.
• Finally, just press OK. And yes! We are done. #### Solution 4: Use the VALUE Function

Lastly, I’ll show how to use the function to solve the problem. We’ll use the VALUE function in this regard.

Steps:

• First, add a helper column.
• Then write the following formula in Cell D5
`=VALUE(C5)`
• Hit the Enter button.  • Then use the SUM formula and you will get the expected result. Read More: [Fixed!] SUM Formula Not Working in Excel (8 Reasons with Solutions)

### 2. Change Calculation Mode

If you keep the calculation mode in Manual mode then it might be a reason and for that Excel SUM formula is not working and returns zero. But it doesn’t happen in the latest version- Excel 365, which can happen in some earlier versions.

Solution:

Always keep the calculation mode to Automatic.

• Click as follows to set Automatic mode: Formulas ➤ Calculation Options ➤ Automatic. Read More: [Fixed!] Why Formula Is Not Working in Excel (15 Reasons with Solutions)

### 3. Remove Non-numeric Characters

If the cells contain non-numeric characters with the numbers then also you will get zero from the SUM formula. Take a look, there are commas with the numbers in my dataset here. Solution:

You can remove them manually but it’s not feasible for a large dataset. So using the Find and Replace tool is the best option.

Steps:

• Select the data range C5:C9.
• Press Ctrl + H to open the Find and Replace
• Then type comma (,) in the Find what box and keep the Replace with box empty.
• Finally, just press Replace All. That tool removed all the commas and the SUM formula is working fine now. ## Practice Section

You will get a practice sheet in the Excel file given above to practice the explained ways. ## Conclusion

I hope the procedures described above will be good enough to solve the problem if the SUM formula is not working and returns 0. Feel free to ask any question in the comment section and please give me feedback.

## Related Articles Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

1. Reply None of these things work (I tried them all and then some). If I highlight the cells, the summary ribbon at the bottom correctly calculates the sum. But SUM and SUMPRODUCT return zero.

I’ve turn auto calculate off and on

• Reply Hello, HPOTTER.
Thanks for your feedback. We think your problem is very specific which is difficult to identify without the file. So, if you would share your Excel file with us then we could find out the issue and hope, we could give you a solution.

2. Reply So these did not help, but my issue as it turned out is the data had an added space after the number, and excel did not know how to handle it.

• Reply Hello JK,
Thanks for your feedback. Your problem is quite rare and unique. So it’s difficult to detect this type of problem without the user’s Excel file. If you would share your file with us, then hopefully we could detect the issue and could give you the exact solution. But temporarily we are suggesting you use the SUM function within the TRIM function, we are showing you a sample formula:
=TRIM(SUM(C5:C9))

The TRIM function will remove all extra spaces. I hope, it will help you.

3. Reply 4. Reply • Reply Hello, Tanael Wawe!

You are welcome. Stay in touch with ExcelDemy to get more helpful content.

Regards
ExcelDemy

5. Reply • Reply Hello, Tanael Wawe!

Thanks for your appreciation. Stay in touch with ExcelDemy to get more helpful content.

Regards
ExcelDemy

6. Reply Hello,

I applied all steps but They didnot solve my 0 problem. My data are like following form in my excel sheet:
0.8020772933959961
15.836971521377563
0.20077300071716309

but when I use sum, then I get null! Any offer will be appreciated. thx

• Reply Al Ikram Amit May 30, 2023 at 11:37 AM

Hello MAHIN,

Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

First of all, you have inserted a leading zero by any of the following means:

• Converting a number into a Text for adding the leading zero

Now, there could be a few reasons why the SUM function in Excel is failing to deliver any results for your data. Here are some recommendations to aid in troubleshooting the issue:

• The cell is previously formatted as Text. After converting them into the Number from Home tab the problem can still persist. • Try to copy your cells and paste them into the new destination. After pasting it in the new cells Trace Error button will appear. Click on that button and select Convert to Number. • Here is the final output after converting it to a number. • Look for hidden characters or spaces: On occasion, Excel may fail to recognize values in the cells as numbers because of hidden characters or trailing spaces. Use the CLEAN feature to get rid of any concealed characters. For instance, if your data is in cell A1 and you want to clean it up, you can use the formula “=CLEAN(A1)” in another cell, and then use the SUM function on the cleaned values.
• Look for any mistakes in other cells: The SUM function may occasionally return null if other cells in the range you’re trying to sum include errors like #VALUE! or #DIV/0!. Examine the other cells in the range, and correct any mistakes you find.

You should be able to fix the Excel SUM function returning null problem using the abovementioned techniques. I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

Regards

Al Ikram Amit

Team ExcelDemy Advanced Excel Exercises with Solutions PDF  