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

Hi,

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.

the picture in your original post shows data starting in column C so I based the formula and the model on that column.

Anyway, glad we could help & best way to thanks people who helped is to Mark as Answer (link at the bottom) the reply/replies that solved your problem (so not this reply ;-). Marking as Answer is also a good indicator for those searching for existing solutions...

Answer 3

Oups, forgot your last point (But I still don't understand why, though, which is frustrating :).

Are you talking about the formula in row 7 and its IF(MOD(COLUMN()... that is causing you a headache? Let me know and I'll try to explain...