Sanjida Mehrun Guria
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.
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.
- After that, you can see that from the drop-down, the Manual option is selected.
- Instead of that, select the Automatic option.
- 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.
- 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.
- 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.
- 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.
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:
Another one is:
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.
- 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)
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.
- 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.
- 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.
