Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP inside SUMPRODUCT = complex problem
I have an excel model quite complex with the followinf layout
(drastically simplified): col A col B col C col D row 1: a 3 a 0.5 row 2 b 4 b 1 row 3: a 5 I need to calculate, by means of a sumproduct function, the calculation: (3*0.5 + 4*1 + 5*0.5) = 1.5 + 4 + 2.5 = 8 I have tried =SUMPRODUCT(B1:B3,VLOOKUP(A1:A3,C1:D2,2,0)) but it does not work. I have also tried the above formula using Ctrl-Shift-Enter, with the same result. Because my model is really complex, I do not want to use auxiliary columns for intermediate results. Is it possible to use SUMPRODUCT the way I need? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP inside SUMPRODUCT = complex problem
Hi,
Try =SUMIF(A1:A3;"a";B1:B3)*SUMIF(C1:C2;"a";D1)+SUMIF( A1:A3;"b";B1:B3)*SUMIF(C1:C2;"b";D1) Please note that you may have to change ";" in ",". Wkr, JP "vsoler" wrote in message ... I have an excel model quite complex with the followinf layout (drastically simplified): col A col B col C col D row 1: a 3 a 0.5 row 2 b 4 b 1 row 3: a 5 I need to calculate, by means of a sumproduct function, the calculation: (3*0.5 + 4*1 + 5*0.5) = 1.5 + 4 + 2.5 = 8 I have tried =SUMPRODUCT(B1:B3,VLOOKUP(A1:A3,C1:D2,2,0)) but it does not work. I have also tried the above formula using Ctrl-Shift-Enter, with the same result. Because my model is really complex, I do not want to use auxiliary columns for intermediate results. Is it possible to use SUMPRODUCT the way I need? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP inside SUMPRODUCT = complex problem
Hi,
The shortest version I came up with uses the idea below: If you rearrange you lookup table to look like this a b 0,5 1 Then you can use: SUMPRODUCT(B1:B3*((A1:A3=C1:D1)*C2:D2)) if you don't want to do that then you can use the longer formula =SUMPRODUCT(B1:B3*((A1:A3={"a","b"})*{0.5,1})) which turns out to be exactly as long as using SUMIF. or the still longer version =SUMPRODUCT(B1:B3*((A1:A3=TRANSPOSE(C1:C2))*TRANSP OSE(D1:D2))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "vsoler" wrote: I have an excel model quite complex with the followinf layout (drastically simplified): col A col B col C col D row 1: a 3 a 0.5 row 2 b 4 b 1 row 3: a 5 I need to calculate, by means of a sumproduct function, the calculation: (3*0.5 + 4*1 + 5*0.5) = 1.5 + 4 + 2.5 = 8 I have tried =SUMPRODUCT(B1:B3,VLOOKUP(A1:A3,C1:D2,2,0)) but it does not work. I have also tried the above formula using Ctrl-Shift-Enter, with the same result. Because my model is really complex, I do not want to use auxiliary columns for intermediate results. Is it possible to use SUMPRODUCT the way I need? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write an Or statement inside Sumproduct? | Excel Worksheet Functions | |||
Complex SUMPRODUCT/VLOOKUP/SUMIF | Excel Worksheet Functions | |||
how can i write complex numbers inside excel cels? | Excel Discussion (Misc queries) | |||
Wildcard character inside sumproduct | Excel Worksheet Functions | |||
Hlookup or Vlookup problem? or wich other solution? Complex Proble | Excel Discussion (Misc queries) |