If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? . |
#4
|
|||
|
|||
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? . |
Thread Tools | |
Display Modes | |
|
|