Thread: Requesting a SUMPRODUCT command that will give a count of rows where all of the follo

Requesting a SUMPRODUCT (or other) command that will give a count of rows where all of the following three things are true:

column A = P
(column B = Q or column B = R)
column C = S

=SUMPRODUCT(--(A1:A10="p"),--(B1:B10="r")+(B1:B10="q"),--(C1:C10="s"))

Martin, minor point, given you are adding the Boolean output of r & q to give effect of OR clause you need not coerce the r test with double unary, the addition operation will coerce by default, ie:

Code:

=SUMPRODUCT(--(A1:A10="p"),(B1:B10="r")+(B1:B10="q"),--(C1:C10="s"))

Another variant would be

Code:

=SUMPRODUCT(--(A1:A10="p"),--ISNUMBER(MATCH(B1:B10,{"q","r"},0)),--(C1:C10="s"))

ok but i had a couple of spare - lying around from an old formula and wanted to use them up lol

I wanna play!

=SUMPRODUCT(--(A1:A10&B1:B10&C1:C10={"prs","pqs"}))

hey, there's only room for one smart-a$$ round here