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

[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

--

 

[Luke M]

Sure is.

=SUMPRODUCT(ISNUMBER(MATCH(A1:A26,Z1:Z7,0))*O1:O26)

 

[PCLIVE]

Thanks. That's what I was looking for.

Paul

--

 

[Lars-Åke Aspelin]

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

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