Sum Products with VLOOKUP

  • Thread starter Thread starter TWJOHN
  • Start date Start date
T

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?
 
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))
 
Perfect. Thank you

T. Valko said:
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





.
Click to expand...
 
Back
Top