sumproduct with vlookup
 

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

sumproduct with vlookup
 

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" skrev i meddelelsen
...
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

sumproduct with vlookup
 

On Sep 22, 7:28*pm, "Per Jessen" wrote:
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" skrev i ...



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- Hide quoted text -

- 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

sumproduct with vlookup
 

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(D 4: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)

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article
,
mohavv wrote:

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