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.

**Table of Contents**hide

**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?

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

**Read More:** **Sum If a Cell Contains Text in Excel (6 Suitable Formulas)**

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

**Read More:** **Sum to End of a Column in Excel (8 Handy Methods)**

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

- Next, use the
**Fill Handle**tool to**copy**the**formula**.

- 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)**

**Similar Readings**

**How to Refresh Formulas in Excel (2 Easy Methods)****[Fixed]: Excel Formula Not Showing Correct Result (8 Methods)****[Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)****[Fixed!] Formula Not Working and Showing as Text in Excel****How to Sum a Column in Excel (6 Methods)**

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

**Read More:** **How to Sum Selected Cells in Excel (4 Easy Methods)**

**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

**[Fixed!] Excel Formulas Not Working on Another Computer (5 Solutions)****How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)****[Solved:] Excel Formula Not Working unless Double Click Cell (5 Solutions)****How to Sum Columns in Excel (7 Methods)****[Solved]: Excel Array Formula Not Showing Result (4 Suitable Solutions)****How to Sum Only Positive Numbers in Excel (4 Simple Ways)****Shortcut for Sum in Excel (2 Quick Tricks)**

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

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.

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.

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.