sumproduct with vlookup

M

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
(D4:D9,B13:D14,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
 
P

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
 
M

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 -
Click to expand...

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
 
D

Domenic

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

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

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

=SUMPRODUCT(1/SUMIF(B14:B15,B4:B9&C4:C9,OFFSET(C14:D15,,LOOKUP(D4:D9,C13:
D13,COLUMN(C13:D13)-COLUMN(C13)),,1)),E4:E9)

Adjust the ranges, accordingly. Note that the currencies listed in
B13:D15 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)
 
Top