Tricky Conditional Count
 

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

Tricky Conditional Count
 

Try this

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

"Steve" wrote:

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

Tricky Conditional Count
 

Barb -

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

I appreciate the assistance.

Steve





"Barb Reinhardt" wrote:

Try this

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

"Steve" wrote:

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

Tricky Conditional Count
 

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

"Steve" wrote in message
...
Barb -

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

"Barb Reinhardt" wrote:

Try this

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

"Steve" wrote:

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?

Tricky Conditional Count
 

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)

"Steve" wrote in message
...
Barb -

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

I appreciate the assistance.

Steve





"Barb Reinhardt" wrote:

Try this

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

"Steve" wrote:

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

Tricky Conditional Count
 

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


"Steve" wrote:

Barb -

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

I appreciate the assistance.

Steve





"Barb Reinhardt" wrote:

Try this

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

"Steve" wrote:

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