[Fixed!] SUM Formula Not Working in Excel (8 Reasons with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

There are multiple reasons behind the SUM formula not working in Excel. In this article, we will try to find out the suspected reasons for this. Afterward, we will show solutions for them as well.


SUM Formula Not Working in Excel: 8 Reasons with Solutions

For example, we have taken a dataset that shows 3 types of fruits’ weekly sales report. Now let’s look at the reasons and solutions when the SUM formula is not working in Excel.

SUM Formula Not Working in Excel


Reason 1: Excel Manual Calculation Is Enabled

When you change any data in the dataset, the final result does not change accordingly. The reason behind this is the manual calculation mode is enabled in Excel.

Solution:

To solve this problem follow the steps below:

  • First, go to the Formulas tab on the upper layer and click the Calculation Options drop-down.

SUM Formula Not Working in Excel

  • After that, you can see that from the drop-down, the Manual option is selected.
  • Instead of that, select the Automatic option.

SUM Formula Not Working in Excel

  • Now, let’s use the SUM formula on cell C12 of the initial dataset to calculate the total sale amount.
=SUM(C5:C11)

  • To check if the process is working or not, we will change some values in the dataset.

SUM Formula Not Working in Excel

  • Finally, you can see that by converting manual calculation to automatic, the SUM formula is working with any set of data.

Read More: Excel Formulas Not Calculating Automatically


Reason 2: Dataset Cells Are in Text Format

In the event of using the SUM formula, it may not show any result because some of the data cells are in text format.

Solution:

Find the solution for this problem below:

  • For this, just change the cell into Number format from Text format. You can find this option in the Number section of the Home tab from the ribbon.

SUM Formula Not Working in Excel

  • Otherwise, right-click on the required cell.
  • Select Format Cells.

  • A Format Cells window opens.
  • After that, you can see that it is in Text format. Change it to the Number format from the Category section.

SUM Formula Not Working in Excel

  • If you are working on accounting or currency or any other format, make sure all the cells are in the same format as well.
  • Otherwise, follow the steps above and convert the cell whichever is applicable for your dataset.

Read More: [Fixed!] Formula Not Working in Excel and Showing as Text


Reason 3: ‘Show Formulas’ Icon Is Turned on

Another reason for the SUM formula not working might be because the Show Formulas icon is turned on.

Solution:

To fix this problem follow the steps here:

  • Go to the Formulas tab in the Excel ribbon.
  • Then check if the ‘Show Formulas’ icon is selected.
  • If yes, then deselect it.
  • Now the SUM formula will work in excel.

SUM Formula Not Working in Excel


Reason 4: Active Circular Reference in Excel

When you insert the SUM formula in a cell and use the same cell as a reference cell. Therefore, it shows any of the circular reference warnings below:

SUM Formula Not Working in ExcelAnother one is:

SUM Formula Not Working in Excel

Solution:

This problem is rare but mostly occurs due to a lack of attention. Simply make sure your reference cells are not overlapping the SUM formula cell.


Similar Readings


Reason 5: Rows or Columns Are Hidden/Deleted

A common mistake is that we often forget when any row or column is hidden in the dataset at any point of using the SUM formula. It results in not showing accurate results despite having the correct formula.

Solution:

The only solution to this is, to check all the rows or columns are active. You can do this by these methods:

  • If the Filter option is active in your dataset, make sure you select all the cells.

SUM Formula Not Working in Excel

  • Another process is to right-click on the hidden cell and then select Unhide.

 


Reason 6: Wrong Use of Parentheses

The most common symbols used as parentheses in excel formulas are comma (,), colon (:) multiplication (*), etc. While using these in the SUM formula, especially if it is a bigger one, the wrong application may result in the SUM formula not working.

Solution:

  • Make sure the SUM formula you insert has the right pattern of parentheses like this:
=SUM(C5:C11)

SUM Formula Not Working in Excel

Note: When you insert any wrong symbol or at any wrong place it will result in an error like this:


Reason 7: Unnecessary Use of Spaces Inside SUM Formula

A single space is a reason for big trouble in the excel SUM formula. It always causes errors in calculation

Solution:

For example, we have put a space in front of the equal (=) of the SUM formula. You can see that it is not showing any result. So make sure the SUM formula is free from any unnecessary spaces.


Reason 8: Wrong List Separator in Excel

The last problem for the SUM formula not working might occur if you insert the wrong list separator. You must know approved list separator for your region. North America uses a comma as the default separator. On the other hand, European countries use semicolons as separators.

Solution:

To find out the list separator of your region follow these steps:

  • First, go to the Control Panel.
  • After that, click on change date, time, or number formats.

SUM Formula Not Working in Excel

  • A new Region window will open. Here, click on Additional Settings.

  • Now check on the List Separator to see the allowed one for your region. For mine, here we use a comma as the separator.

SUM Formula Not Working in Excel

  • After confirming this, use it in your SUM formula. Surely it will work from now.

Download Practice Workbook

Here you can download the sample workbook to practice by yourself.


Conclusion

In this article, we tried to provide you with maximum solutions when your SUM formula is not working in Excel. Hope this was a helpful article. Let us know if you have any more solutions or suggestions in the comment box.


Related Articles

Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo