find product of 2 cells and a match index in a row, then sum the result

[dalexcel]

I would like to find the Sum of the No. Units*price*relevant exchange rate according to month and currency.

Here is my two sets of data:
1. Info

	A	B	C	D
	Date	Currency	No Unit	Unit Price
1	05/Jan	EUR	100	1.15
2	07 Jan	USD	100	20
3	09 Feb	HKD	200	100
4	10 Mar	EUR	300	4
5	28 Mar	USD	700	9

<tbody>
</tbody>

2. Exchange rate matrix (Range named FX)

	A	B	C	D
1		Jan	Feb	Mar
2	EUR	1.1	1.2	1.3
3	HKD	0.13	0.14	0.15
4	USD	1	1	1

<tbody>
</tbody>

Essentially what I would like to find is one formula to Sum the products from column C, D, and the index match according to FX the range.

In other words

(C1*D1*Indexmatch to find exchange rate according to A1 and B2)+(C2*D2*Indexmatch to find exchange rate according to A2 and B2) etc......

I would like to avoid adding a column that finds the product for each row. Hence I'm asking for a formula to sum all info at once.

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E
1	Date	Currency	No Unit	Unit Price
2	05-Jan	EUR	100	1.15	126.5
3	07-Jan	USD	100	20	2000
4	09-Feb	HKD	200	100	2800
5	10-Mar	EUR	300	4	1560
6	28-Mar	USD	700	9	6300
7
8		Jan	Feb	Mar
9	EUR	1.1	1.2	1.3
10	HKD	0.13	0.14	0.15
11	USD	1	1	1
Sheet5

Cell Formulas
Range	Formula
E2	=INDEX($B$9:$D$11,MATCH($B2,$A$9:$A$11,0),MATCH(TEXT($A2,"mmm"),$B$8:$D$8,0))*C2*D2

 

[radian89]

Hi Dalexcel,

what you looking for gonna generate very long formula. adding 1 column certainly make it short. take a look at this.

change your month with number, 1, 2, 3, to make the formula work.

exchange rate will use index & match.
the total will use sumproduct.

but if you want to combine it all then the formula will be

D4*E4*INDEX($C$16:$E$18,MATCH(C4,$B$16:$B$18,0),MATCH(MONTH(B4),$C$15:$E$15,0)) +
D5*E5*INDEX($C$16:$E$18,MATCH(C5,$B$16:$B$18,0),MATCH(MONTH(B5),$C$15:$E$15,0)) +
.
.
.
.

[/IMG]