Odd problem with formula counting Yes and No

[Meenie]

Excel 2003
I have a formula that counts all the "yes"s in a range of cells which it
does correctly. in the next cell I have the exact same formula counting the
"no"s in the same range of cells - it returnes "0" but there are two "no"s in
the range.
I have completely erased the formula and typed it in again, I have run
"evaluate" on it, I've checked the formatting of the cells... I can't get it
to count the "no"s. !!!!
I have three different ranges that I'm doing this for and I copied the
formula to count the no's and entered it into a different cell to count a
different range, and it counts the no's...
I'm stumped. Anyone have an idea?
thanks, Meenie

 

[Jacob Skaria]

check whether there are any space in your entries and it is exacly 'no' and
then try

or try
=COUNTIF(a:a,"*no*")

If this post helps click Yes

 

[Ken]

I think you need to post the formula. There are lots of ways to count
things and lots of ways to screw it up. Simply copying the formula
could change the range (depending on your use of absolute and relative
references) and what looks like a " no" may not match the "no" you use
in the formula, due to leading or trailing spaces; and there are lots
of other things that someone in this group will spot for you right
away; provided you give enough information.

Ken

 

[Meenie]

Oh my, it works but I don't know why. I don't see any spaces and it's exactly
'no' but thanks bunches! much better formula!

 

[T. Valko]

I don't see any spaces

Yeah, that's the problem with leading/trailing spaces, you can't see them!
<g>

 

[Ken]

Meenie

You can test to see if something that looks like "no" is really equal
to "no" with a formula

="no"=A1 (replace A1 with any cell reference that wasn't counted with
your formula, but, was counted with Jacob's formula).

I suspect you will get False.

His formula takes care of your problem by counting anything that
contains the string "no"; which shouldn't be a problem since
everything looks like "yes' or "no"; but, be aware that it will also
count such things as "snore" and "I am not a crook".

Good luck.

Ken