COUNTIF Bug in Excel 2003

[Guest]

1) Open a new workbook.
2) In cells A2, A4 and A6 enter the word "dog".
3) In cell B1 enter the formula:
=COUNTIF(A:A,"<>dog")

In Excel 2003, I get 65533.
In Excel 97, 2000 and 2002, I get 3 (for the three blank cells between A1
and A6)

Thinking that it might be a corrupted Excel 2003 install, I asked a friend
to check his results--and he confirmed my experience. I'm using version
11.6355.6408 SP1.

 

[Mangesh Yadav]

I am using 2003 and 65533 should be the correct answer.

Mangesh

 

[Guest]

It's either a bug in 2003 or in the earlier versions--because there shouldn't
be different answers from a function as old as COUNTIF. I called it a bug in
2003, just because of the weight of history.

 

[Anne Troy]

Incredible. I read this post last time through, even verified that what you
said was true, at least in 97 and 2003, and never even noticed it was you.
Hi!!!
*******************
~Anne Troy

www.OfficeArticles.com

 

[Biff]

Hmmmm.....

I'm using 2002.

Try this...

Clear cells A2, A4 and A6

=COUNTIF(A:A,"<>dog") then returns 6.

Clear the formula from B1 then type it in again. The formula still returns
6.

Biff

 

[Guest]

Biff,
I can confirm your observation for Excel 2002. It is apparently looking at
the "used range" for column A, and not resetting it when the data is deleted.
It will reset the formula (if you select the cell, click in the formula bar,
then hit Enter) when the file is saved or when you execute a VBA command to
count the rows in the used range ActiveSheet.UsedRange.Rows.Count