Excel - Summing numbers by currency format

Question

Hi Everyone,

I am experiencing difficulties with Excel 2013.

I am working on a reporting sheet, showing sales in various countries in the past month.

I would like to sum the numbers by currency format : all euro sales summed up in a cell, those in dollars in another one, etc. 

My question is : how can I make excel sort and add up numbers by currency format?

An image being better than a thousand words, I posted an example below.

In the example, I'd like to show the sum of (number of units sold*price per unit) for all countries, sorted by currency (col. D, F, H) ; the presentation is shown as such because prices may vary between countries.

thank you very much for your help!

Answer 1

Hi again... ;-)

The only reason I can think of re. "Unit" everywhere in row 7 is that you did not copy to the right the formula in C7.

In D7 you should read

=IF(MOD(COLUMN(),2),"Unit",VLOOKUP(C$6,$C$12:$D$21,2,FALSE))

in E7 you should read

=IF(MOD(COLUMN(),2),"Unit",VLOOKUP(D$6,$C$12:$D$21,2,FALSE))

...

I posted a sample on OneDrive. You will see that I moved the Country/Currency table to another sheet and named that range CountryCurrCodes. So my formula in C7 is now

=IF(MOD(COLUMN(),2),"Unit",VLOOKUP(C$6,CountryCurrCodes,2,FALSE)). Copied right into D7, E7... this alternatively returns "Unit" or the Currency Code that matches the Country Name enterred in row C6, E6, G6...

Not sure what else to do/say for now. Again, if you could upload your file (or a sample that reflect its structure) I could potentially identify the source of the problem

Answer 2

Would it be possible you upload your workbook on OneDrive and post a link to the file in your next reply?

Answer 3

Hi,

The currency symbol is a global Windows setting in the regional an language part of the control panel.

I don't know where you get your data from but I guess that all values with a currency symbol are imported as text.

A format does not change the value and the installed currency symbol is even not in the cell, it is only a view whilst a non-valid currency symbol will be included in the cell.

I do not see a simple solution for this, maybe importing all data as text. Note, while importing a csv you cannot prevent Excel to interpret the data, but there are work-arounds.

Answer 4

Hey Lz,

Thanks for your dedication!

I have very carefully recopied your formulas, and it works.

I believe the reason for my sheet originally not working is that I had an empty column in C, so I had shifted your formula one cell to the right.

and apparently it doesn't work this way.

But I still don't understand why, though, which is frustrating :)

Anyway load of thanks for your help! I have a fully functional worksheet thanks to you.

Answer 5

Thanks you,

That is what I thought indeed!

Cheers.