[Solved!] Currency Sum Not Working in Excel (6 Suitable Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

=SUM(C5:C9)
  • Then, press the Enter button and see the result.

Apply SUM function for Currency in Excel

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.

Checking cells of dataset before correction of sum not working

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.


Currency Sum Is Not Working in Excel: 6 Solutions

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.

Apply Paste Special to solve Currency Sum Not Working

  • Finally, press the OK button and look at the dataset.

We get the sum finally.


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.

Correct data manually to solve Sum Not Working

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


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.

Solve Currency Sum Not Working using text to columns feature

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


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.

Currency Sum Not Working solve by Flash Fill

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


Solution 5: Use Excel VALUE Function

The 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) 

Currency Sum Not Working solve by VALUE function

  • 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) 

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.

Excel VBA to solve Currency Sum Not Working

  • Run the VBA code by pressing the F5 button.

We can see errors removed and get the currency sum.


Download Practice Workbook

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


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. You can give your suggestions in the comment box below.


Related Articles


<< Go Back to Sum in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo