Sum Products with VLOOKUP
 

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?

Sum Products with VLOOKUP
 

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


"TWJOHN" wrote in message
...
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?

Sum Products with VLOOKUP
 

Perfect. Thank you

"T. Valko" wrote:

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


"TWJOHN" wrote in message
...
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?


.

Sum Products with VLOOKUP
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"TWJOHN" wrote in message
...
Perfect. Thank you

"T. Valko" wrote:

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


"TWJOHN" wrote in message
...
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?


.