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


--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=540123

SUMPRODUCT + multi conditions
 

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" wrote in
message ...

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

SUMPRODUCT + multi conditions
 

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

SUMPRODUCT + multi conditions
 

You're welcome. Thanks for the feedback!

Biff

"BernzG" wrote in
message ...

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