We are very used to sum to total term. When any individual buys a certain number of products from a shop and wants the bill, needs to sum up all the amounts. Or if any shop wants to get the total sale of a day needs to sum bills of each individual. But before that need to get all the bills in the same currency. Here, we are going to discuss the problem of currency sum not working in Excel and its solution with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**How to Detect If Sum Is Not Working in Excel**

First, we need to know the reason why the sum is not working in Excel. Suppose, we have the following dataset to get the sum.

- We apply the following
**SUM function**on**Cell C10**.

`=SUM(C5:C9)`

- Then, press the
**Enter**button and see the result.

What is this? The result is zero! This means the currency sum is not working. What would be the probable reason?

- Let’s check the data of
**Range C5:C9**.

The **COUNTA function** checks the numbers of non-empty cells, and the **COUNT function** checks cells containing numbers only. Both of the functions count based on the given criteria.

We can see no cells contains numbers. They may be in text or other custom formats.

- To check the format of the data, go to the
**Number Format**section of the**Number**group.

We can see the given data are in **General** format. Also, notice the formula bar. There is a leading apostrophe symbol at the start of the number. Because of this, an error is showing in each cell of the dataset. The status bar is also not showing the total, only counting the cells.

**6 Suitable Solutions If Currency Sum Is Not Working in Excel**

**Solution 1: Use of Paste Special Multiplication to Convert Numbers in Proper Format**

We will use the **Paste Special** to remove the error in the data. This will ultimately solve the problem of currency sum in Excel.

**📌 ****Steps:**

- Go to any blank cell of the dataset and insert
**1**.

We will use this **1** to multiply the given data. As we are using **1**, the magnitude of the data will not change.

- Now, select the cell where we insert
**1**. - Then, select the
**Copy**option from the**Clipboard**group.

We can also use **Ctrl+ C** to copy data.

- Now, select
**Range C5:C9**. - Go to
**Paste**from the**Clipboard**group. - Now, select the
**Paste Special**option.

**Ctrl + Alt+ V** is the keyboard shortcut for **Paste Special**.

- The
**Paste Special**window appears. - Mark
**Values**from the**Paste**segment and**Multiply**from the**Operation**segment.

- Finally, press the
**OK**button and look at the dataset.

We get the sum finally.

**Read More: ****[Fixed] Excel COUNT Function Not Working**

**Solution 2: Use of Error Checking Option**

In this section, we will correct the errors from the dataset manually.

**📌 ****Steps:**

- Click on
**Cell C5**. - We will see an error notification on the left side. Click on the down arrow.
- Choose the
**Convert to Number**option.

- Look at the dataset.

We can see the error has been removed.

- Similarly, remove the errors of the rest of the data.

Finally, after removing all the errors, we get the sum successfully.

**Read More: ****[Fixed!] Iterative Calculation Not Working in Excel**

**Similar Readings**

**[Fixed!] Excel Scrolling Too Many Rows (2 Easy Solutions)****[Solved]: Macro Settings Greyed out in Excel****[Fixed!] IF Function Is Not Working in Excel (4 Quick Solutions)****[Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)****Excel Data Validation Greyed Out (4 Reasons with Solutions)**

**Solution 3: Use Text to Columns Wizard to Convert Numbers in Text to Number Format**

We will use the **Text to Columns** feature to convert text to numbers to get the sum.

**📌 ****Steps:**

- First, select
**Range C5:C9**. - Select
**Text to Columns**from the**Data**tab.

**1st**step of the**Convert****Text to Columns Wizard**window appears.- Mark the
**Delimited**option, then click on the**Next**button.

- The
**2nd**step appears after that. Mark the**Other**Put the**apostrophe**(**‘**) symbol on the box. - Again, click on the
**Next**button.

- Choose the
**General**option. - Click on the
**Finish**option lastly.

- Look at the dataset.

We get the currency sum successfully.

**Read More: ****[Fixed!] Formulas Are Not Calculating Automatically in Excel**

**Solution 4: Use of Flash Fill Feature to Get Currency Sum Properly**

In this section, we will use the **Flash Fill **feature to correct the errors to get the currency sum in Excel.

**📌 ****Steps:**

- First, we add a column on the right side.

- Insert the data of
**Cell C5**into**Cell D5**manually.

- Select the
**Range D5:D9**. - Choose
**Flash Fill**of the**Data Tools**group from the**Data**tab.

- Look at the dataset.

We can see all the errors have been corrected and get the sum. We can use the keyboard shortcut **Ctrl + E** to apply the **Flash Fill**.

**Read More: ****[Fixed!] Auto Fill Options Not Showing in Excel**

**Similar Readings**

**[Fixed!] Macros Not Working in Excel (3 Possible Solutions)****[Fixed!] Excel COUNTIF Function Not Working for String “True”****[Fixed!] Excel Filter Stops at Blank Row (4 Possible Solutions)****[Fixed!] Unshare Workbook Greyed Out in Excel****[Fixed] Excel Date Filter Not Working**

**Solution 5: Use Excel VALUE Function**

**VALUE function**converts a text string that represents a number to a number.

The **NUMBERVALUE function** converts a text to number in a locale-independent manner.

In this section, we will apply the **VALUE** function to solve the errors to get the sum.

**📌 ****Steps:**

- Go to
**Cell D5**and put in the following formula.

`=VALUE(C5)`

- Press the
**Enter**button and drag the**Fill Handle**icon.

We get the currency sum accurately. We can also use the **NUMBERVALUE** function instead of the **VALUE** function. And the formula will look like this:

`=NUMBERVALUE(C5)`

**Read More: ****[Solved] Excel Formatting Not Working Unless Double Click Cell**

**Solution 6: Use an Excel VBA Code to Get Currency Sum Correctly**

Here, we will use VAB code to remove errors from the data and convert them to numbers.

**📌 ****Steps:**

- Go to the bottom of the worksheet containing the sheet name section.
- Press the right button of the mouse.
- Select the
**View Code**option from the**Context Menu**.

- The VBA window appears.
- Choose the
**Module**option from the**Insert**tab.

- Put the following VBA code on the module.

```
Sub Solve_Errors()
Dim range_1 As Range
On Error Resume Next
Set range_1 = Selection.SpecialCells(xlCellTypeConstants, 23)
On Error GoTo errHandler
If Not range_1 Is Nothing Then
Cells.SpecialCells(xlCellTypeLastCell).Offset(0, 1).Copy
range_1.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd
Else
MsgBox "No Constant Found"
End If
exitHandler:
Application.CutCopyMode = False
Set range_1 = Nothing
Exit Sub
errHandler:
MsgBox "Change Not Possible"
Resume exitHandler
End Sub
```

- Select the
**Range C5:C9**.

- Run the VBA code by pressing the
**F5**button.

We can see errors removed and get the currency sum.

**Read More: ****[Fixed!] Merge Cells Button Is Greyed Out in Excel**

**Conclusion**

In this article, we described how to remove errors from data in Excel and then solve the problem of the currency sum not working. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy** and give your suggestions in the comment box.

## Related Articles

**[Fixed] Excel Files That Are Macros Enabled But Not Working****[Solved!]: COUNTIF Function Is Returning 0 in Excel (4 Solutions)****[Fixed!] Excel Hyperlink Is Not Redirecting Properly****[Fixed!]: Unable to Enable Macros in Excel (5 Quick Solutions)****[Solved]: COUNTBLANK Not Working in Excel****[Fixed!] Excel Hyperlink Keeps Coming Back (5 Quick Solutions)****[Solved:] Excel Not Filtering Entire Column (3 Quick Fixes)**