COUNTIF woes

[Thermometer]

I'm trying to do something simple, or at least I thougth I was.
If I have the word BLAH in cell C20 and then in cell D20 I have the
formula =COUNTIF(C20,LEFT(C20,2)="BL")
why is the result 0 (zero) instead of 1 ?
Thanks for any advice.

[Gary Brown]

BL does not equal BLAH, therefore no cells found, therefore found = 0.
Try...
=If(Left(C20,2)="BL",1,0)

HTH,
Gary Brown


"Thermometer" wrote:

> I'm trying to do something simple, or at least I thougth I was.
> If I have the word BLAH in cell C20 and then in cell D20 I have the
> formula =COUNTIF(C20,LEFT(C20,2)="BL")
> why is the result 0 (zero) instead of 1 ?
> Thanks for any advice.
>
>

[JulieD]

Hi

because the formula evaluates in this order
=COUNTIF(C20,"BL"="BL")
=COUNTIF(C20,TRUE)
=COUNTIF("BLAH",TRUE)
which = 0

if you're doing more than one cell in the range to check the following
should give you what you want
=SUMPRODUCT(--(LEFT(C20:C30,2)="BL"))

Cheers
JulieD

"Thermometer" <thermometer@excite.com> wrote in message
news:1111768797.800255.157500@g14g2000cwa.googlegroups.com...
> I'm trying to do something simple, or at least I thougth I was.
> If I have the word BLAH in cell C20 and then in cell D20 I have the
> formula =COUNTIF(C20,LEFT(C20,2)="BL")
> why is the result 0 (zero) instead of 1 ?
> Thanks for any advice.
>

[Thermometer]

But then how would I expand that to count all the occurrances of BL in
a column, such as c20:c:500 ?? Sorry, I should have explaned my
problem further. One cell was not a good example. Tha's where the
COUNTIF comes in, I think.

[duane]

taking Julie's solution......

=SUMPRODUCT(--(LEFT(C20:C500,2)="BL"))

[Alan Beban]

Thermometer wrote:
> But then how would I expand that to count all the occurrances of BL in
> a column, such as c20:c:500 ?? Sorry, I should have explaned my
> problem further. One cell was not a good example. Tha's where the
> COUNTIF comes in, I think.
>
=COUNTIF(C20:C500,*BL*) or, if C20 contains BLAH

=COUNTIF(C20:C500,"*"&LEFT(C20,2)&"*")

Alan Beban

[Dave Peterson]

Just a small typo on the first formula:
=COUNTIF(C20:C500,"*BL*")

(added a pair of double quotes)

Alan Beban wrote:
>
> Thermometer wrote:
> > But then how would I expand that to count all the occurrances of BL in
> > a column, such as c20:c:500 ?? Sorry, I should have explaned my
> > problem further. One cell was not a good example. Tha's where the
> > COUNTIF comes in, I think.
> >
> =COUNTIF(C20:C500,*BL*) or, if C20 contains BLAH
>
> =COUNTIF(C20:C500,"*"&LEFT(C20,2)&"*")
>
> Alan Beban

--

Dave Peterson

[Thermometer]

Thanks all for the advice. I appreciate the help.
Therm