sumproduct with vlookup

[mohavv]

Hi, I have a table with amount in different currencies. I'd like to
add them using a exchange rate.

The table looks like this:

A1 B C D E
2
3 LCY
4 2009 E2 EUR 500
5 2009 E2 EUR 500
6 2009 E2 EUR 500
7 2009 E2 JPY 10000
8 2009 E2 JPY 10000
9 2009 E2 JPY 10000
10
11 31,500
12
13 EUR JPY
14 2009E2 0.5 65
15 200901 0.55 70


this is what I have up to now: =SUMPRODUCT(--(1/HLOOKUP
(D49,B1314,2,FALSE))*(E4:E9))

this only uses the EUR 2009E2 rate (0.5) and gives me an answer og
63000. Correct answer should be 3,462

Can this be solved?

Cheers,

Harold

 

[Per Jessen]

Hi Harold

I can not see what you are trying to do from your example. Can you describe
in words what you need.

Regards,
Per

 

[mohavv]

Hi Harold

I can not see what you are trying to do from your example. Can you describe
in words what you need.

Regards,
Per

"mohavv" <[email protected]> skrev i meddelelsen








- Show quoted text -

I'm trying to sum all the values in Column E while multiplying them
with the corresponding exchange rate based on column D from rows
13:15.

(500 / 0.5) + (500 / 0.5) + (500 / 0.5) + (10000 / 65) + (10000 / 65)
+ (10000 / 65) = 3462

does this help?

Harold

 

[Domenic]

If you only have the two currencies, as per the sample data, try...

=SUMPRODUCT(--(D49="EUR"),1/SUMIF(B14:B15,B4:B9&C4:C9,C14:C15),E4:E9)+S
UMPRODUCT(--(D49="JPY"),1/SUMIF(B14:B15,B4:B9&C4:C9,D1415),E4:E9)

If in fact you have many currencies, try...

=SUMPRODUCT(1/SUMIF(B14:B15,B4:B9&C4:C9,OFFSET(C1415,,LOOKUP(D49,C13:
D13,COLUMN(C1313)-COLUMN(C13)),,1)),E4:E9)

Adjust the ranges, accordingly. Note that the currencies listed in
B1315 need to be listed in alphabetical order, as per the sample data,
which lists the column for EUR first and the column for JPY second.
However, I would suggest the following alternative, which uses a helper
column...

F4, copied down:

=1/INDEX($C$14:$D$15,MATCH(B4&C4,$B$14:$B$15,0),MATCH(D4,$C$13:$D$13,0))*
E4

Then, try...

=SUM(F4:F9)