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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Currency[/TD]
[TD]No Unit[/TD]
[TD]Unit Price[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/Jan[/TD]
[TD]EUR[/TD]
[TD]100[/TD]
[TD]1.15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]07 Jan[/TD]
[TD]USD[/TD]
[TD]100[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]09 Feb[/TD]
[TD]HKD[/TD]
[TD]200[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10 Mar[/TD]
[TD]EUR[/TD]
[TD]300[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]28 Mar[/TD]
[TD]USD[/TD]
[TD]700[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]


2. Exchange rate matrix (Range named FX)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EUR[/TD]
[TD]1.1[/TD]
[TD]1.2[/TD]
[TD]1.3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]HKD[/TD]
[TD]0.13[/TD]
[TD]0.14[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]USD[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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]