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 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.

SUBSTITUTE is case sensitive.

A1 = This dog is real big

When in doubt use this syntax:

SUBSTITUTE(UPPER(A1),UPPER("DOG"),...

You can use LOWER( ) in place of UPPER. The point is to make the case of the source and the old text the same.

 

[JoeMo]

Well, I learned something - didn't realize that SUBSTITUTE function is case sensitive. Thanks.

 

[T. Valko]

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.

You're welcome. Thanks for the feedback!

 

[Blade Hunter]

SUBSTITUTE is case sensitive.

A1 = This dog is real big

When in doubt use this syntax:

SUBSTITUTE(UPPER(A1),UPPER("DOG"),...

You can use LOWER( ) in place of UPPER. The point is to make the case of the source and the old text the same.

Why the second one?

 

[T. Valko]

Why the second one?

Just to be "safe".

A1 = This dog is real big

You want the case of A1 to be the same case as "old_text".

=SUBSTITUTE(A1,old_text,...