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

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

--

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

Sure is.

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PCLIVE" wrote:

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

--

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

Thanks. That's what I was looking for.

Paul

--

"Luke M" wrote in message
...
Sure is.

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PCLIVE" wrote:

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

--

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

On Wed, 15 Jul 2009 12:17:15 -0400, "PCLIVE"
wrote:

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