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.
- Greg GLv 77 years agoFavorite 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.
- illigLv 44 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.