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.

**Table of Contents**hide

## 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.