EXCEL: Group by date and calculate the sum based on currency?


I have the below columns in Excel( "___" is simply to illustrate that these are different columns).

My goal is to group the data according to their "Value Date" and calculate the sum of the amounts in "Trace Ccy1 Amount" & "Trade Ccy2 Amount" columns based on the currency "Ccy1" & "Ccy2" columns.

So basically to group all the trades based on the value date and then calculate the sum of a certain currency (i.e. GBP) which can be in either one of the columns (Ccy1 or Ccy2, depending on the direction of the trade) based on the value date. And output the amount in a any cell.

Entity Id___Trade Date___Trade Number___Instrument___Portfolio___Mnemonic___Customer___Short Name___Value Date___Maturity Date___Symbol___Direction___Ccy 1___Trade Ccy1 Amount___Trade Price___Internal Rate___Ccy 2___Trade Ccy2 Amount___Trader Id

Is this too complicated of a task to handle in Yahoo Answers?

Value Date is in the following format: mm/dd/yyyy

Ccy1 & Ccy2: i.e. GBP

Trade Ccy1 Amount & Trade Ccy2 Amount: number

Thanks in advance.

2 Answers

  • Greg G
    Lv 7
    7 years ago
    Favorite Answer

    Sounds like you may need to use SUMPRODUCT (or maybe SUMIFS if you have Excel 2007 or later)

    I would also set up several data validation cells so you can easily select the date. ccy1 & 2,.

    Since it appears you want the trade amounts, use this:

    =SUMPRODUCT(--(V_Date=X1) * (ccy1=X2) * (ccy2=X3), trade_ccy1_amount)


    V_Date is the range containing your dates

    X1 is the selected date to find in the V_Date range

    ccy1 is the 1st currency range

    X2 is the currency type to find in the ccy1 range

    ccy2 is the 2nd currency range

    X3 is the currency type to find in the ccy2 range

    trade_ccy1_amount is the range for your 1st trade amount

    Repeat the formula using trade_ccy2_amount range to get the 2nd value.

    Let me know if you need any further assistance setting this up.

    • Commenter avatarLogin to reply the answers
  • illig
    Lv 4
    4 years ago

    No i do no longer... yet basically as others don't have a say in how I develop my young toddlers, I don't have a say in how others develop their young toddlers. i'm against indoctrination yet i won't be in a position to work out a thank you to combat it with out removing the freedoms of others. I mean what if somebody instructed you it became into incorrect to allow your toddlers play with Ninja turtles action figures because of the fact it advocates vigilantism? So mutually as my very own opinion is that teachings in the bible directed in the direction of youthful toddlers that glorify the violence and genocide in the bible utilising action figures are certainly incorrect.. i won't ignore that different individuals would think of the comparable approximately many different action figures and the ideals they symbolize. as quickly as you start up there it won't stop at that, exceptionally quickly any violent action figures would be banned.. as an occasion i think of Bratz dolls coach ladies to be self-based and shallow, yet is it my place to tell human beings they are in a position to't purchase them for their toddlers? No it fairly isn't any longer. i would not want somebody else telling me what my young toddlers can play with that's suitable, so I won't do it to different individuals.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.