Fixing SUM Formula Not Working in Excel

This guide will explain how to start fixing SUM formula when it returns 0 in Excel.

Cell formatting is the most common reason the SUM formula does not work in Excel. We’ll explain a few methods you can use to convert your range into valid numerical input.

The SUM formula requires one or more ranges as input. Non-numeric values and blank cells are ignored. If the resulting sum of your range is 0, then it’s likely that the entire range is composed of non-numeric or blank cells.

Let’s take a look at a quick example! Suppose you must sum up all expenses across various categories in a table. We can use the SUM function to add all values in a specific range.

However, the SUM function returns a 0 instead. How can we fix this in Microsoft Excel?

There are a few possible reasons why the SUM formula returns a ‘0’. 

First, your Excel program may be using Manual Calculation mode. Worksheets set to this mode require you to confirm that you want Excel to calculate every formula explicitly. We can switch to Automatic Calculation mode through an option in the Formulas tab.

Second, your values may contain unnecessary characters that prevent Excel from reading them as numerical data. For example, the string ‘100,’ is interpreted as text because of the extra ‘,’ character.

The SUM function may also not work because the cells have been formatted as text. In the following guide, we will show you a few methods you can use to convert these text values into numerical data.

Now that we know some reasons why the SUM formula may return a 0, let’s take a look at a few examples that use some of our possible solutions.

A Real Example of Fixing SUM Formula Returning 0 in Excel

In the following section, we will provide a few sample tables where the SUM formula returns a 0. We’ll also go in-depth on the formulas and tools used in these examples.

First, the example below shows a SUM function that is not working and returns 0. If we were to look at the range B2:B6, we can see that each cell contains a green triangle on the top left. This marker warns that the cell contains numerical values stored as text.

In the next section, we will show you multiple ways to convert the text values into numbers.

The example below contains multiple cells that are read as text rather than numbers. This is because of the commas left over at the end. We can remove these commas quickly through the Find & Replace tool.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you want to try fixing your sheet’s SUM function using these methods, head over to the next section to read our step-by-step breakdown on how to do it!

How to Fix SUM Formula Not Working and Returns 0 in Excel

This section will guide you through each step needed to fix the SUM formula not working in Microsoft Excel. You’ll learn how we can fix numerical values stored as text through several different methods.

Follow these steps to fix your SUM formula:

1. The first method involves the use of Excel’s Error Checking feature. Select every cell in your range that contains the green error marker.
   
2. After making your selection, click on the yellow error icon to reveal a dropdown menu. Click the option labeled ‘Convert to Number’.
   
3. Your range should now be numerical data. In the example below, our SUM formula in cell B8 has added up our newly fixed range successfully.
   
4. We can also use the VALUE function to convert our strings into numerical data. The benefit of using a formula is that our original column remains unmodified.
   
5. Type the VALUE function into the formula bar and add the cell holding the string as input.
   
6. Hit the Enter key to evaluate the function. In the example below, we can now see that cell C2 no longer contains the green marker in the corner.
   
7. Use the Fill Handle tool to convert the rest of the values in column B.
   
8. Now that we have valid numerical data in column C, we can modify our SUM function in cell B8 to use the new cell range.
   
9. If your cells contain unneeded characters, we can use the Find and Replace tool to convert these strings to valid numbers. First, select all cells in your SUM range that contain unnecessary characters.
   
10. Next, click on Ctrl + H to bring up the Find and Replace dialog box.
    
11. Type the unnecessary character you want to remove into the ‘Find what:’ text box. Leave the ‘Replace with;’ text box empty. Click on the Replace All button to delete all instances of the indicated character.
    
12. The tool will send a pop-up message indicating how many replacements were made. You may have to perform this step several times for each non-numeric character you need to remove.
    
    

This step-by-step guide should be all you need to start fixing SUM formula not working in Excel. Our guide has given you some of the most common solutions you can try in case your SUM function keeps returning a ‘0’ result.

The SUM formula is just one example of a mathematical function you can use in Excel. Our website covers hundreds of other Excel methods and functions you can apply on your own spreadsheets. 

With so many other Excel functions available, you can surely find one that suits your use case. 

Are you interested in learning more about what Excel can do? Subscribe to our newsletter to look into the latest Excel guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe