Share via

Excel - Summing numbers by currency format

Anonymous
2016-07-06T12:03:00+00:00

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!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Lz._ 38,106 Reputation points Volunteer Moderator
    2016-07-07T15:23:28+00:00

    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

    0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-07-06T12:41:10+00:00

    In I8 enter the formula and drag down

    =SUM(D8,H8)

    In J8 enter the formula and drag down

    =SUM(F8)

    0 comments
  2. Anonymous
    2016-07-06T12:52:17+00:00

    Hello Karthick Gunasekaran,

    Thanks for your answer! However your solution is not what I am looking for.

    I may have 25 countries, 5 currencies and a lot of products and varying order of countries, so I am looking for an automatic way to sort things.

    Otherwise, I agree that your solution has a great simplicity to it ;)

    0 comments
  3. Anonymous
    2016-07-06T13:09:58+00:00

    Hi G-Hmorning,

    Thanks for your reply.

    Initially i was doubted. i am not a good judge here.

    Each country have same currency or it differs?

    0 comments
  4. Lz._ 38,106 Reputation points Volunteer Moderator
    2016-07-06T16:09:59+00:00

    Hi,

    Would something like this work for you (sample with 10 countries)?

    EDIT: No non-numeric value should exist in column B. The Total column titles must contain, as their last 3 letters, the currency code as defined in D12:D21 (table in C12:D21 can be moved in another sheet)

    In C7:

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

    copy right as necessary. Row 7 can later be hidden

    in O9:

    =SUMPRODUCT((($C$7:$N$7=RIGHT(O$6,3))*$C9:$N9)*($B9:$M9))

    copy right and down as necessary

    0 comments