Tricky Conditional Count

[Guest]

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

 

[Guest]

Try this

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

 

[Guest]

Barb -

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

I appreciate the assistance.

Steve

 

[David Biddulph]

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

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

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"))