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

Consider a dataset that shows the weekly sales report for three fruit types.

Reason 1: Excel Manual Calculation Is Enabled

Solution:

• Go to the Formulas tab on the top bar and click the Calculation Options drop-down.

• Select the Automatic option.

• Let’s use the SUM formula on cell C12 of the initial dataset to calculate the total sale amount.

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

• You can see that by converting manual calculation to automatic, the SUM formula is updating in real time.

Reason 2: Dataset Cells Are in Text Format

Solution:

• Change the cell into Number format. You can find this option in the Number section of the Home tab from the ribbon.

• Right-click on the required cell.
• Select Format Cells.

• A Format Cells window opens.
• Change it to the Number format from the Category section. Click on OK to confirm.

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

Reason 3: ‘Show Formulas’ Icon Is Turned on

Solution:

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

Reason 4: Active Circular Reference in Excel

You might’ve used the result cell in the formula. It will show a circular reference warning below:

Or this one:

Solution:

This problem is rare but mostly occurs due to a lack of attention. However, Excel doesn’t have a way of fixing this automatically. Make sure your reference cells are not overlapping the SUM formula cell.

Reason 5: Rows or Columns Are Hidden/Deleted

Solution:

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

• Right-click on the hidden cell and then select Unhide.

Reason 6: Wrong Use of Parentheses

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

Solution:

• Make sure the SUM formula you insert has the right pattern of parentheses like this:

Reason 7: Unnecessary Use of Spaces Inside SUM Formula

For example, we have put a space in front of the equals sign (=) in the SUM formula. You can see that it is not showing any results.

Solution: 

Remove any unnecessary spaces.

Reason 8: Wrong List Separator in Excel

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:

• Go to the Control Panel.
• Click on change date, time, or number formats.

• A new Region window will open. Click on Additional Settings.

• Check on the List Separator to see the default one for your region.

• Use it in your Excel formulas. Surely it will work from now.

Download Practice Workbook

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

Related Articles

• How to Refresh Formulas in Excel
• [Fixed!]: Excel Formula Not Showing Correct Result
• Excel Formulas Not Calculating Automatically
• [Fixed!] Excel Formulas Not Working on Another Computer
• [Fixed!] Formula Not Working in Excel and Showing as Text
• [Solved:] Excel Formula Not Working unless Double Click Cell
• [Solved]: Excel Formulas Not Updating Until Save
• [Solved]: Excel Array Formula Not Showing Result

<< Go Back To Formulas not Working in Excel | Excel Formulas | Learn Excel