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

 

[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]

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.

 

[Alan Beban]

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)

 

[Thermometer]

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