COUNTIF woes
 

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.

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.

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" wrote in message
oups.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.

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.

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

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

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