Excel SUM Function Not Adding Correctly: Troubleshooting Tips

What do you do when you use the SUM function and it doesn’t add up the values correctly?

If you are a regular user of Microsoft Excel, you may have encountered an issue where the sum function does not add up correctly. This can be a frustrating issue, especially when you are dealing with large amounts of data and need to ensure accuracy. The sum function is one of the most commonly used functions in Excel, and it is essential to know how to troubleshoot it when it is not working correctly.

There are several reasons why the sum function may not be adding up correctly. One of the most common reasons is that the cells you are trying to sum contain text instead of numbers. Another reason may be that the cells are formatted as text, which prevents Excel from recognizing them as numbers. Additionally, the sum function may not work correctly if there are hidden rows or columns in the worksheet.

If you are experiencing issues with the sum function in Excel, there are several steps you can take to troubleshoot the problem. By identifying the root cause of the issue, you can take appropriate action to ensure that your calculations are accurate and reliable. In this article, we will explore some of the most common reasons why the sum function may not be adding up correctly and provide step-by-step instructions on how to fix the problem.

Check Input Cells

When working with Excel, it can be frustrating when the SUM function doesn’t add up the expected values.

There are several common causes for this issue, and the first place to start is with the inputs used in the SUM functions parameters.

Incorrect Cell References

One of the most common causes of the SUM function not adding correctly is incorrect cell references . If you accidentally reference the wrong cells , the function will not calculate the correct sum.

Double-check your formulas to ensure that you have referenced the correct cells.

=SUM(A1,A2,A3)

In the above SUM formula example the cell references are A1 , A2 , and A3 . You will want to check these cells are those you wish to add.

=SUM(A1:B3)

In this SUM formula example the cell reference is a range of cells with the left-most cell starting in cell A1 and the bottom right-most cell ending in B3 . You will want to check the contents of these cells are those you wish to accumulate.

To see the cells being used in the SUM formula you can use the Trace Precedents feature of Excel as demonstrated here:

This feature is very handy when dealing with Named Ranges , where it can be challenging to know where the source of this range of cells can be found.

Hidden Rows or Columns

If you have hidden rows or columns in your spreadsheet, the SUM function may include those cells, but as they are hidden it can be difficult to determine if the SUM function is working properly.

Unhide any hidden rows or columns to check your SUM function value is correct.

The SUM formula will correctly calculate the sum even on hidden rows and columns, but it can be difficult to determine the cell is giving the correct result if you cannot see the inputs.

Here’s an example of how to unhide a row:

Incorrect Data Type Inputs

The SUM function only works with numerical data . If you have non-numerical data in the range you are trying to sum, Excel will not replace the cell containing the non-numerical data with 0.

Here’s an example demonstrating the SUM formula being used on cells containing text:

As you can see from the above result, when adding a range of cells containing non-numeric values 0 is returned.

Therefore, if the SUM formula you are using doesn’t return the expected value, check the cells being referenced contain numeric values.

Troubleshooting Tips

If you are experiencing issues with the Excel SUM function not adding correctly, you can try a few troubleshooting tips to resolve the problem.

Check Cell Formatting

One common reason for the SUM function not working correctly is incorrect cell formatting. Make sure that all cells containing numbers are formatted as numbers and not as text.

You can check this by selecting the cells and looking at the formatting options in the Format > Cells menu.

If the cells are formatted as text, you can change the format to number by selecting the cells and then clicking on the Number format category in the Home tab.

Check Parentheses

Another reason the SUM function may not be working correctly is errors in the formula itself.

I have found this common when there are values or other functions being used as parameters in the formula, and I have not closed off a set of parentheses properly.

Verify Function Arguments

If the SUM function is returning a #FIELD! error it could be due to incorrectly entering a comma to separate each of the inputs and instead using a full-stop.

For example, if you enter the following formula you will get a #FIELD! error:

=SUM(A1.B5)

Notice the formula contains a full-stop instead of a comma to sum the two cells A1 and B5 .

Another common error is the #NAME! error which means the cell reference or named range used cannot be found. The following will return an error as there is no cell or named range labelled A :

=SUM(A) 

SUM Not Working: Summary

Excel is a powerful tool, but it can be frustrating when the SUM function does not add correctly. I have shared some of the common reasons why the SUM function has not worked as expected in my experiences.

If you are experiencing similar frustrations check the following:

• The cells and range are correct (use Trace Precedents and unhide cells if necessary).
• The cells and range contain errors or text.
• The parameters and arguments don’t have proper closing parentheses or incorrect syntax is used.

To avoid these issues, it is important to double-check the range selection, ensure that the range contains only numerical values, and unhide or unfilter any hidden or filtered cells.

By understanding these common issues and taking the necessary steps to avoid them, users can ensure that the SUM function works correctly and accurately in their Excel spreadsheets.