SUMPRODUCT + multi conditions

Hi,

My current formula works okay but I wish to add more complexity to it.
=SUMPRODUCT(--(Others!$A$2:$A$2000=$A2),--(Others!$B$2:$B$2000="U"),--(Others!$I$2:$I$2000=1),--(Others!$X$2:$X$2000))

where $A2 is the name of a product ( 1st array )
and Range Others!$B$2:$B$2000 contains data Q, A, AS, U, MAS

What I am wanting to do is extend the 2nd array to include any of the following ( U, AS & MAS ). How do I do this ?

Cheers,
Bernz

Hi!

Try this:

=SUMPRODUCT(--(Others!$A$2:$A$2000=$A2),--(ISNUMBER(MATCH(Others!$B$2:$B$2000,{"U","AS","MAS"},0))),--(Others!$I$2:$I$2000=1),Others!$X$2:$X$2000)

Biff

"BernzG" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> My current formula works okay but I wish to add more complexity to it.
> =SUMPRODUCT(--(Others!$A$2:$A$2000=$A2),--(Others!$B$2:$B$2000="U"),--(Others!$I$2:$I$2000=1),--(Others!$X$2:$X$2000))
>
> where $A2 is the name of a product ( 1st array )
> and Range Others!$B$2:$B$2000 contains data Q, A, AS, U, MAS
>
> What I am wanting to do is extend the 2nd array to include any of the
> following ( U, AS & MAS ). How do I do this ?
>
> Cheers,
> Bernz
>
>
> --
> BernzG
> ------------------------------------------------------------------------
> BernzG's Profile:
> http://www.excelforum.com/member.php...o&userid=22949
> View this thread: http://www.excelforum.com/showthread...hreadid=540123
>

Hi Biff,

Thanks - that has worked perfectly

You're welcome. Thanks for the feedback!

Biff

"BernzG" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff,
>
> Thanks - that has worked perfectly
>
>
> --
> BernzG
> ------------------------------------------------------------------------
> BernzG's Profile:
> http://www.excelforum.com/member.php...o&userid=22949
> View this thread: http://www.excelforum.com/showthread...hreadid=540123
>