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
**1**, simply mark^{st}step**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:**

### Why Is Excel Not Giving Me A Sum?

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.

### Can I Use the SUM Formula with Non-Contiguous Cells?

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.

### What Are 3 Ways to Use the SUM Function in Excel?

The 3 ways to use the Sum Function in MS Excel are:

- Using AutoSum.
- Writing a Sum Formula.
- Copying Sum Function to Other Columns.

### What Do I Do If My SUM Formula Shows #VALUE! Error?

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.

### How Do I Get Excel to Stop Returning 0?

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.

### What's The Difference Between SUM And AutoSum?

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.

### Are There Any Limitations to Excel's SUM Formula?

There are various limitations to Excel’s SUM formula, but the major drawback is- tremendously large datasets might impact the performance.

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