[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 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.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


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?

Number Stored As Text

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.

Use Convert to Number if the SUM formula is not working and returns 0

Now see, we got the values as numbers and the SUM formula worked properly.

Use Convert to Number if the SUM formula is not working and returns 0


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.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

  • In the first step, mark Delimited.
  • Later, press Next.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

  • After that mark Tab and press Next.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

  • Mark General in the last step.
  • Finally, just press Finish.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

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.

Apply Paste Special Command if the SUM formula is not working and returns 0.

  • Next, select the cells and right-click your mouse.
  • Then select Paste Special from the Context menu.

Apply Paste Special Command if the SUM formula is not working and returns 0.

  • 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.

Use the VALUE Function if the SUM formula is not working and returns 0.

Use the VALUE Function if the SUM formula is not working and returns 0.

  • Then use the SUM formula and you will get the expected result.


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.

Change Calculation Mode if the SUM formula is not working and returns 0.


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.

Remove Non-numeric Characters if the SUM formula is not working and returns 0.

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.

Remove Non-numeric Characters if the SUM formula is not working and returns 0.

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.

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo