SUMPRODUCT or SUMIF if any values in a range equal any values in another range

  • Thread starter Thread starter PCLIVE
  • Start date Start date
P

PCLIVE

Let's say I have values in range A1:A26.
Next I have some values in range Z1:Z7.

I'd like to do some sort of SUMIF or SUMPRODUCT that if any of the values in
Z1:Z7 are in the range A1:A26, I'd like to SUM the corresponding values in
O1:O26.

Is there a simpler way to do this than daisy-chaining a bunch of SUMIFs
together?

Thanks,
Paul

--
 
Let's say I have values in range A1:A26.
Next I have some values in range Z1:Z7.

I'd like to do some sort of SUMIF or SUMPRODUCT that if any of the values in
Z1:Z7 are in the range A1:A26, I'd like to SUM the corresponding values in
O1:O26.

Is there a simpler way to do this than daisy-chaining a bunch of SUMIFs
together?

Thanks,
Paul
Click to expand...


This could probably be written more simple, but anyway.
Assuming the values in Z1:Z7 are all different, try this:

=SUMPRODUCT(MMULT(--(MMULT(A1:A26,--(COLUMN(OFFSET(Z1,,,7))>0))-TRANSPOSE(Z1:Z7)=0),--(ROW(Z1:Z7)>0)),O1:O26)

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke
 
Back
Top