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. Lz._ 38,106 Reputation points Volunteer Moderator
    2016-07-07T06:52:32+00:00

    Hi,

    Concerned about a potential non-numeric value in column B I reworked it (still with Helper row #7)

    in C7:

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

    copy right as necessary

    in S9:

    =SUMPRODUCT((($D$7:$R$7=RIGHT(S$6,3))*$D9:$R9)*(($C$7:$Q$7="unit")*$C9:$Q9))

    copy right and down as necessary

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Lz._ 38,106 Reputation points Volunteer Moderator
    2016-07-07T10:56:34+00:00

    I didn't mention it but you can also apply Conditional Formatting starting S9 to auto apply to appropriate currency format to your Totals. Given that you have 5 currencies, 5 basic rules does it

    0 comments
  4. Anonymous
    2016-07-07T12:56:55+00:00

    Hello Lz,

    I'm sorry, but I haven't been able to copy the formula in my table.

    Even replacing all the cells references by the right ones, I still get only "unit" in every cell in line 7

    And the sum is not right either, despite all modification I tried to implement on your formula, the total formula comes up with "#name?"..

    Could you maybe explain a bit more? or are there some values I should replace? is there a value to enter in the "column()"? some format?

    Thanks!

    0 comments