Doing a countif against a substring of the range to be scanned
 

I need to see how many cells in a particular range have a substring that
matches the text against which I'm searching. So, for example, I've tried the
following formula:

COUNTIF(left(B2:B292,10),"="&left(cell("contents", P242),10))

I knew that this wouldn't work, but I was hoping that being able to evaluate
the formula might lead me in the right direction. Excel won't accept the
formula at all, though, so no chance to evaluate.

I also tried this formula, which does let me evaluate:

=COUNTIFS(B1:B292,"="&LEFT(P242,10))

but the evaluation really just tells me that this formula doesn't give me
what I need either.

Anyone have any ideas?

Thanks.

Doing a countif against a substring of the range to be scanned
 

Huh. Never mind, I figured it out. Turns out this formula does what I need:

=COUNTIF(B1:B292,"="&LEFT(P242,10)&"*")

Should have dug around a little bit more before posting.

"jetboy2k" wrote:

I need to see how many cells in a particular range have a substring that
matches the text against which I'm searching. So, for example, I've tried the
following formula:

COUNTIF(left(B2:B292,10),"="&left(cell("contents", P242),10))

I knew that this wouldn't work, but I was hoping that being able to evaluate
the formula might lead me in the right direction. Excel won't accept the
formula at all, though, so no chance to evaluate.

I also tried this formula, which does let me evaluate:

=COUNTIFS(B1:B292,"="&LEFT(P242,10))

but the evaluation really just tells me that this formula doesn't give me
what I need either.

Anyone have any ideas?

Thanks.