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
|