Sumif/Sumproduct

[JRenn]

Hi
On Sheet 1, I have rows of data as follows:
Column A: Name of client
Column B: Amount (value) in original currency
Column C: Currency
Column D: Transaction Date
Column E: Settlement Date

The Name of the client may appear on multiple rows and may have different currencies.

On Sheet 2, I have a currency table with the currency exchange rates to convert to USD as follows:
Column A: Currency (i.e. USD, GBP, CAD etc)
Column B: The exchange rate

What I am attempting to do is to sum the converted values for each particular client in 1 step.

Taking it 1 step further, I would like the same sum within time buckets i.e. if the difference between the two dates falls within 30, 60 and 90 days etc?

Thanks for you assistance.

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Name	Amount	Currency	Transaction Date	Settlement Date		Currency	exchange rate
2	Name5	20	GBP	27/07/2017	05/10/2017		USD	1
3	Name3	30	GBP	01/08/2017	30/09/2017		GBP	1.35
4	Name2	10	USD	08/08/2017	06/11/2017		CAD	0.8
5	Name2	50	CAD	31/07/2017	20/08/2017
6	Name2	10	CAD	01/08/2017	20/09/2017
7	Name3	20	GBP	26/07/2017	14/09/2017
8	Name2	30	CAD	30/07/2017	19/08/2017
9	Name1	50	GBP	08/08/2017	17/10/2017
10	Name5	10	USD	13/08/2017	22/09/2017
11	Name1	50	CAD	22/08/2017	01/10/2017
Sheet1

Excel 2013/2016
	J	K	L
1	Name	USD	Period
2	Name2	72	60
3	Name3	67.5	90
Sheet1

Cell Formulas
Range	Formula
K2	=SUMPRODUCT(($A$2:$A$11=$J2)*(($E$2:$E$11-$D$2:$D$11)<=$L2),SUMIF($G$2:$G$4,$C$2:$C$11,$H$2:$H$4),$B$2:$B$11)

 

[JRenn]

Brilliant - many thanks AlanY. Much appreciated.

 

[AlanY]

you're welcome