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

Hello,

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.

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

Relevance

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)

Where:

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

• Login to reply the answers
• Login to reply the answers