Multiply 2 columns based on condition then sum them up

[goodguy]

	A	B	C	D	E	F
1	Chocolate	EUR	8		BRL/CAD	3.1430
2	Coffee bean	BRL	5		EUR/CAD	0.6760
3	Apple	USD	3		USD/CAD	0.7560
4	Macaroon	EUR	10
5	Candy	USD	2
6	Total	CAD

<tbody>
</tbody>

I have two tables as shown above.

I want to find the total in CAD according to their local currencies, i.e., C1/F2 + C2/F1 + C3/F3 + C4/F2 + C5/F3

I tried vlookup and index match embedded in sumproduct and sumifs but with no luck

It seems that I'll have to look up in column E with B1*, B2*, B3*, etc.

Can anyone please help? Thanks.

 

[fhqwgads]

if you could add in a separate column for CAD this might help

Book1
	A	B	C	D	E	F
1	Chocolate	EUR	8	11.83432	BRL/CAD	3.143
2	Coffee bean	BRL	5	1.590837	EUR/CAD	0.676
3	Apple	USD	3	3.968254	USD/CAD	0.756
4	Macaroon	EUR	10	14.7929
5	Candy	USD	2	2.645503
6	Total	CAD		34.83181
Sheet22

Cell Formulas
Range	Formula
D1	=C1/VLOOKUP(B1&"*",$E$1:$F$3,2,FALSE)
D2	=C2/VLOOKUP(B2&"*",$E$1:$F$3,2,FALSE)
D3	=C3/VLOOKUP(B3&"*",$E$1:$F$3,2,FALSE)
D4	=C4/VLOOKUP(B4&"*",$E$1:$F$3,2,FALSE)
D5	=C5/VLOOKUP(B5&"*",$E$1:$F$3,2,FALSE)
D6	=SUM(D1:D5)

i cant figure out how to do it without adding a helper column of extra data and just putting it all in one formula in C6 though

sorry if that didnt help

 

[Marcelo Branco]

Try

=SUMPRODUCT(C1:C5/SUMIF(E1:E3,"*"&B1:B5&"*",F1:F3))

Hope this helps

M.

 

[Marcelo Branco]

A little simpler...

=SUMPRODUCT(C1:C5/SUMIF(E1:E3,B1:B5&"*",F1:F3))

M.

 

[goodguy]

if you could add in a separate column for CAD this might help

	A	B	C	D	E	F
1	Chocolate	EUR	8	11.83432	BRL/CAD	3.143
2	Coffee bean	BRL	5	1.590837	EUR/CAD	0.676
3	Apple	USD	3	3.968254	USD/CAD	0.756
4	Macaroon	EUR	10	14.7929
5	Candy	USD	2	2.645503
6	Total	CAD		34.83181

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet22​

Worksheet Formulas Cell Formula D1 =C1/VLOOKUP(B1&"*",$E$1:$F$3,2,FALSE) D2 =C2/VLOOKUP(B2&"*",$E$1:$F$3,2,FALSE) D3 =C3/VLOOKUP(B3&"*",$E$1:$F$3,2,FALSE) D4 =C4/VLOOKUP(B4&"*",$E$1:$F$3,2,FALSE) D5 =C5/VLOOKUP(B5&"*",$E$1:$F$3,2,FALSE) D6 =SUM(D1:D5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

i cant figure out how to do it without adding a helper column of extra data and just putting it all in one formula in C6 though

sorry if that didnt help

Thanks fhqwgads. Yes, I tried to avoid creating extra columns/rows because this is just a simple version of my whole file. But thanks for your help anyway!

 

[goodguy]

A little simpler...

=SUMPRODUCT(C1:C5/SUMIF(E1:E3,B1:B5&"*",F1:F3))

M.

Thanks Marcelo, this is exactly what I was looking for. Truly appreciated!!