COUNTIF is not working

[Excel_VBA]

I've got a very simple formula. It is =COUNTIF(A8,"dog") and it is returning a 0. Cell A8 contains "The dog is blue"

The COUNTIF cell and cell A8 are in general format. I tried changing the way the formula calculates from Automatic to Manual and back to Automatic again. Not working.

I'm using Excel 2007. Any ideas what is wrong?

 

[T. Valko]

I've got a very simple formula. It is =COUNTIF(A8,"dog") and it is returning a 0. Cell A8 contains "The dog is blue"

The COUNTIF cell and cell A8 are in general format. I tried changing the way the formula calculates from Automatic to Manual and back to Automatic again. Not working.

I'm using Excel 2007. Any ideas what is wrong?

As written, your formula will only work if the cell contains only the word dog.

Try it like this:

=COUNTIF(A8,"*dog*")

The * asterisks are wildcards which has the meaning of "count if this cell contains the substring "dog" anywhere within the cell".

 

[Blade Hunter]

=(len(a8)-len(substitute(upper(a8),"dog","")))/3

Edit: Do you want to count the occurences of dog in A8 or just return 1 if dog is in there somewhere?

 

[JoeMo]

This returns a 1 if "dog" is present;

=IFERROR(COUNTIF(A8,"* dog *"),0)+IFERROR(COUNTIF(A8,"dog *"),0)+IFERROR(COUNTIF(A8,"*dog"),0)

But will not count an instance of dog as a substring (e.g dogtown won't count).

 

[Blade Hunter]

This will count any instance of dog in the cell;

=IFERROR(COUNTIF(A8,"* dog *"),0)+IFERROR(COUNTIF(A8,"dog *"),0)+IFERROR(COUNTIF(A8,"*dog"),0)

But will not count an instance of dog as a substring (e.g dogtown won't count).

Mines a bit smaller

 

[JoeMo]

Mines a bit smaller

Yours doesn't work for me if I put "The blue dog" in A8; returns 0 (evaluates to 0/3).

 

[Blade Hunter]

Thats really weird, all it's doing is comparing the size diff between the string and the Uppercase string with DOG replaced.

 

[Excel_VBA]

Try it like this:

=COUNTIF(A8,"*dog*")

The * asterisks are wildcards which has the meaning of "count if this cell contains the substring "dog" anywhere within the cell".

It worked. I see the problem now. Thank you for your suggestion. Thank you to the others who also replied. You solutions may come in handy in the future. Now I'll be able to sleep better tonight.

 

[JoeMo]

Thats really weird, all it's doing is comparing the size diff between the string and the Uppercase string with DOG replaced.

I think use of UPPER is the problem if "dog" is lower case it appears that UPPER("The blue dog") is evaluated as NOT containing the word "dog" so there is no substitution.
This works:

=(LEN(A8)-LEN(SUBSTITUTE(UPPER(A8),"DOG","")))/3

 

[Blade Hunter]

Ahhhhh right you are, bloody typo in my rush .