Using SUMIF inside SUMPRODUCT => complex problem (suite)

[vsoler]

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,D12),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

 

[T. Valko]

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,C12,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.