Using VLOOKUP inside SUMPRODUCT => complex problem

[vsoler]

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?

[barry houdini]

VLOOKUP doesn't accept a range for the first argument, try using SUMIF,
i.e.

=SUMPRODUCT(B1:B3,SUMIF(C1:C2,A1:A3,D1:D2))

--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=129722

[JP Ronse]

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" <vicent...@gmail.com> wrote in message
news:6aca3612-3fcb-452d...@b18g2000vbl.googlegroups.com...

[vsoler]

On Aug 29, 4:49 pm, barry houdini <barry.houdini.

Excellent!

It works perfectly. thank you

[Shane Devenshire]

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))*TRANSPOSE(D1:D2)))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire