Fixing Excel Sum Formula Not Working Returns 0: A Step-by-Step Guide

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.

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:

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^st step, simply mark 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.

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.

Try Now       Buy Now

* Free version of the product only previews recoverable data.

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