SUMPRODUCT and LOOKUP Table

[Getting to Know VBA]

Hi, I am seeking to create a formula that multiples columns B and C and applies the appropriate currency exchange (refer to second table). The result should be the sum total of all rows, being $24.29.

	A	B	C
1	FX	Unit Price	Open Interest
2	AUD	$100	0.0%
3	SGD	$500	-10.0%
4	GBP	$400	5.6%
5	AUD	$650	4.2%

<tbody>
</tbody>

6	Rate	FX
7	1.00	AUD
8	1.83	GBP
9	0.88	SGD
10	1.12	USD

<tbody>
</tbody>

As an example row 3 should calculate as follows: ($500*-10%)*0.88.

Your assistance is greatly appreciated.

 

[PaddyD]

assuming your data is in a1:c10 as per above, your example is:

=(B3+(B3*C3))*INDEX($A$7:$A$10,MATCH(A3,$B$7:$B$10,0))

...copy up / down as required. I'd do each calculation explicitly first then sum the results, rather than trying to stick it all in a mega-formula.

 

[Getting to Know VBA]

Thanks Paddy. I'm good with the functions 'Index' and 'Match'. The issue I have is with the "...copy up / down". I'm constrained by the need to compute and capture the result of the sum total of all rows in a single cell, hence l'm leaning towards applying the SUMPRODUCT function, but don't quite know how to create the formula for this.

 

[PaddyD]

Can you describe how you arrive at the result "being $24.29."?

 

[Getting to Know VBA]

Sure, as follows:

Row 2: $100*0.0%*1.00 = $0.00
Row 3: $500*-10.0%*0.88 = -$44.00
Row 4: $400*5.6%*1.83 = $40.99
Row 5: $650*4.2%*1.00 = $27.30
TOTAL = $24.29

 

[PaddyD]

Sorry for the delay - haven't been around much. With thanks to a few friends:

=SUMPRODUCT($B$2:$B$5,$C$2:$C$5,SUMIF($B$8:$B$11,$A$2:$A$5,$A$8:$A$11))