Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMIF inside SUMPRODUCT = complex problem (suite)
This post is an evolution of the one I posted this morning.
I have an excel model quite complex with the followinf layout (somehow simplified): col A col B col C col D Col E Col F row 1: a 3 a 0.5 a 2 row 2 b 4 b 1 b 3 row 3: a 5 row 5: 1 I need to calculate, by means of a sumproduct function, the following: if cell B5 contains 1: (3*0.5 + 4*1 + 5*0.5) = 1.5 + 4 + 2.5 = 8 if cell B5 contains 2: (3*2 + 4*3 + 5*3) = 6 + 12 + 15 = 33 Cell B5 changes very often Following barry Houdini's suggestion, I have tried =SUMPRODUCT(B1:B3,CHOOSE(B5,SUMIF(C1:C2,A1:A3,D1:D 2),SUMIF (E1:E2,A1:A3,F1:F2)) .... which happens to work, but only if I use Ctrl-Shift-Enter. Is there anyway to get rid of the Ctrl-Shift-Enter? Because my model is really complex, I do not want to use auxiliary columns for intermediate results. Is it possible to achieve the desired result? Thank you again |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMIF inside SUMPRODUCT = complex problem (suite)
I get a different result whe B5=2.
If B5=2 I get 28 Try it like this: =SUMPRODUCT(B1:B3,LOOKUP(A1:A3,IF(B5=1,C1:D2,E1:F2 ))) No error checking! It assumes that B5 will be either 1 or 2. Also, it assumes that your data in C1:C2 and E1:E2 is sorted in ascending order as is demonstrated in your sample data. -- Biff Microsoft Excel MVP "vsoler" wrote in message ... This post is an evolution of the one I posted this morning. I have an excel model quite complex with the followinf layout (somehow simplified): col A col B col C col D Col E Col F row 1: a 3 a 0.5 a 2 row 2 b 4 b 1 b 3 row 3: a 5 row 5: 1 I need to calculate, by means of a sumproduct function, the following: if cell B5 contains 1: (3*0.5 + 4*1 + 5*0.5) = 1.5 + 4 + 2.5 = 8 if cell B5 contains 2: (3*2 + 4*3 + 5*3) = 6 + 12 + 15 = 33 Cell B5 changes very often Following barry Houdini's suggestion, I have tried =SUMPRODUCT(B1:B3,CHOOSE(B5,SUMIF(C1:C2,A1:A3,D1:D 2),SUMIF (E1:E2,A1:A3,F1:F2)) ... which happens to work, but only if I use Ctrl-Shift-Enter. Is there anyway to get rid of the Ctrl-Shift-Enter? Because my model is really complex, I do not want to use auxiliary columns for intermediate results. Is it possible to achieve the desired result? Thank you again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP inside SUMPRODUCT = complex problem | Excel Worksheet Functions | |||
Problem with SUMPRODUCT(SUMIF(INDEX formula linking to external fi | Excel Worksheet Functions | |||
Complex SUMPRODUCT/VLOOKUP/SUMIF | Excel Worksheet Functions | |||
SUMIF and SUMPRODUCT with INDIRECT formula problem | Excel Discussion (Misc queries) | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions |