Totaling of different currencies in a column separately

krishco68

New Member
Joined
Nov 28, 2018
Messages
12
Dear Experts,
I have a range of currencies in a single column, I want to have totals currency wise separately.
the example values are given below. I want the 3 currencies totals separately in another column.

GBP 428.00
GBP 264.11
GBP 121.22
GBP 26.40
GBP 10.75
GBP 219.53
GBP 75.58
GBP 10.75
₦2,000.00
₦1,000.00
₦850.00
₦550.00
₦4,000.00
₦4,000.00
₹ 7,000.00
₹ 2,900.00
₹ 600.00
₹ 1,000.00
₹ 2.88
₹ 160.00
₹ 150.00
₹ 140.00
₹ 140.00
₹ 1,500.00



<colgroup><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
you might try three helper cells that each test for the currency symbol

i.e. =IF(Left(cell),4)= "GBP" etc and use a subtotal in the top row

Excel Workbook
ABCD
1GBP??
21156.341240013592.88
3GBP 428.00428  
4GBP 264.11264.11  
5GBP 121.22121.22  
6GBP 26.4026.4  
7GBP 10.7510.75  
8GBP 219.53219.53  
9GBP 75.5875.58  
10GBP 10.7510.75  
11?2,000.00 2000 
12?1,000.00 1000 
13?850.00 850 
14?550.00 550 
15?4,000.00 4000 
16?4,000.00 4000 
17? 7,000.00  7000
18? 2,900.00  2900
19? 600.00  600
20? 1,000.00  1000
21? 2.88  2.88
22? 160.00  160
23? 150.00  150
24? 140.00  140
25? 140.00  140
26? 1,500.00  1500
Sheet1
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
if you are able to use PowerQuery try:

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "name", each Text.Trim([curr],{"0".."9",",","."," "})),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"name", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"curr"}),
    #"Inserted Kept Characters" = Table.AddColumn(#"Replaced Value", "Kept Characters", each Text.Select([curr], {",", ".", "0".."9"}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Kept Characters",{{"Kept Characters", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"name"}, {{"Sum", each List.Sum([Kept Characters]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[name]), "name", "Sum", List.Sum)
in
    #"Pivoted Column"[/SIZE]

GBP
1156.34​
12400​
13592.88​
 

krishco68

New Member
Joined
Nov 28, 2018
Messages
12
Sir,
Where I can put this code, please guide me.
I am not using the pivot table, I am using normal worksheet.
Regards,
Murali Krishna R.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

- what is your excel version?
- this is not PivotTable but PowerQuery
- all is in normal worksheet :)
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

If you don't have PowerQuery you will need to get free add-in from Microsoft and install proper version (32 or 64 bit)
after that select your range and change it to Excel Table (Ctrl+T)
rename header to curr, PowerQuery is case sensitive so curr and Curr is not the same.
select any cell in your table then find and use From Table
in Power Query Editor use Advanced Editor and replace all code there with code from the post
change the name of table in this line
Code:
Source = Excel.CurrentWorkbook(){[Name="[B][COLOR="#FF0000"]Table13[/COLOR][/B]"]}[Content]
to the name of your table
then use Close&Load
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
if you want vba you need to wait for someone who will do that for you

have a nice day
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-ge...ferent-currencies-in-a-column-separately.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,473
Messages
5,596,356
Members
414,061
Latest member
JJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top