Tricky Conditional Count

S

Steve

I need to do a conditional count (how many rows are there where the following
conditions are both true). One condition is, does a cell that contains
multiple text strings (DEF5678, GHI6789, ABC1234) contain the text string
ABC1234? If that condition is true, is the second condition also true? Tell
me how many records there are where both conditions are true.

I'm very familiar with array formulas
{=sum((array1=ABC1234)*(array2=test2))}, and I'm familiar with searching for
text strings (=countif(B2:B100,"*"ABC1234"*").

Any thoughts?

Steve
 
B

Barb Reinhardt

Try this

=SUMPRODUCT(--(ISNUMBER(SEARCH("ABC1234",A10:A12))),--(B10:B12="test"))
 
S

Steve

Barb -

That did it, thanks. Teach me what the double -- does for the formula,
please.

I appreciate the assistance.

Steve
 
D

David Biddulph

The "double unary minus" changes a boolean (false or true) to a number 0 or
1 so that you can multiply.
 
T

Teethless mama

You can use a single "-" no quotes, if you have even criterias, like 2, 4 , 6
and so on...

=SUMPRODUCT(-(ISNUMBER(SEARCH("ABC1234",A10:A12))),-(B10:B12="test"))
 
Top