Sum Products with VLOOKUP

[TWJOHN]

Is it possible to sum the product of 2 columns, 1 column being the result of
a VLOOKUP function, without creating a third column for the individual row
result?
Here is my problem:
Column A contains a list of activities in various countries
Column B contains the currency code in which those activities are priced
Column C contains the price of the activity in the currency designated in
col B
A (separate) look up table has the exchange rates to my currency
Is there a formula I can use at the bottom of col C to have the total price
in my currency?

I have tried this formula, but it returns '#VALUE!':
=SUM(C1:C50*VLOOKUP(B1:B50,'Currency Rates'!$A$3:$D$10,4,FALSE))
I have also tried SUMPRODUCT, but it returns the same answer.

At the moment I have inserted a column D, which holds the result of col C
multiplied by the looked up currency rate, and then put the resulting sum of
col D at the bottom of col C (so that I can hide col D). This is not an
elegant solution and makes adjustments to the table awkward.

Any suggestions?

 

[T. Valko]

As long as your currency conversion table is sorted in ascending order
something like this will work...

......B..........C
1..Code....Price
2..C..........10
3..A..........5
4..B..........7
5..A..........6

Currency conversion table:

......F..........G
1..Code....Conversion factor
2..A..........1.5
3..B..........2.8
4..C..........0.9

So, the conversion would be:

10*0.9 = 9
5*1.5 = 7.5
7*2.8 = 19.6
6*1.5 = 9

For a total of: 45.1

=SUMPRODUCT(C2:C5*LOOKUP(B2:B5,F2:G4))

 

[TWJOHN]

Perfect. Thank you

As long as your currency conversion table is sorted in ascending order
something like this will work...

......B..........C
1..Code....Price
2..C..........10
3..A..........5
4..B..........7
5..A..........6

Currency conversion table:

......F..........G
1..Code....Conversion factor
2..A..........1.5
3..B..........2.8
4..C..........0.9

So, the conversion would be:

10*0.9 = 9
5*1.5 = 7.5
7*2.8 = 19.6
6*1.5 = 9

For a total of: 45.1

=SUMPRODUCT(C2:C5*LOOKUP(B2:B5,F2:G4))

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!