SUMPRODUCT + multi conditions

[BernzG]

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

 

[Biff]

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]

Hi Biff,

Thanks - that has worked perfectl

 

[Biff]

You're welcome. Thanks for the feedback!

Biff