SUMPRODUCT with VLOOKUP

[buff0x]

Hi All,

I have the following table

A	B	C
Item1	28	651
Item2	78	749
Item3	19	1403

<tbody>
</tbody>

And I have the following formula which works.
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

What I want to do is include vlookups into the sumproduct to pick them 3 items up from a big table of items.

Thanks

Paul

 

[a massive zebra]

Hi All,

I have the following table

A	B	C
Item1	28	651
Item2	78	749
Item3	19	1403

<tbody>
</tbody>

And I have the following formula which works.
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

What I want to do is include vlookups into the sumproduct to pick them 3 items up from a big table of items.

Thanks

Paul

Try this:

=SUMPRODUCT(IF(A2:A4={"Item1","Item2","Item3"},B2:B4),IF(A2:A4={"Item1","Item2","Item3"},C2:C4))/SUM(SUMIF(A2:A4,{"Item1","Item2","Item3"},B2:B4))

Entered with CTRL+SHFT+ENTR, not just enter. Change the row range as required.

 

[buff0x]

Try this:

=SUMPRODUCT(IF(A2:A4={"Item1","Item2","Item3"},B2:B4),IF(A2:A4={"Item1","Item2","Item3"},C2:C4))/SUM(SUMIF(A2:A4,{"Item1","Item2","Item3"},B2:B4))

Entered with CTRL+SHFT+ENTR, not just enter. Change the row range as required.

This done what I needed it to do, thank you.