Prev: SUMIF with NAME RANGE
Next: Need some help BAD with conditional formatting
From: jetboy2k on 29 Mar 2010 13:19
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.
From: jetboy2k on 29 Mar 2010 13:24
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.
 | 
Pages: 1
Prev: SUMIF with NAME RANGE
Next: Need some help BAD with conditional formatting