Countif restricts itself to the usedrange. This should be well known - it
is one of the advantages of countif - but it can be misused.


=COUNTIF(A:A,"<")

works fine since any cell with a value will be in the used range. For
Frank's example, it returns 0 for me and I believe Frank has used the wrong
example or misunderstood the consideration.

=Countif(A:A,"=")
could be problematic (depends on your expectation) since it would only
return blank cells in the used range. It depends on your expectation.

--
Regards,
Tom Ogilvy



"Frank Kabel" wrote in message
...
Hi
tried in in Excel 2000 and Excel 2003 with fresh workbooks.
If you like I can email you my sample file

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Frank,

Never seen this, and it doesn't happen on my laptop, XL2000, XP Pro.


"Frank Kabel" wrote in message
...
Hi
I would strongly recommend not to use this syntax :-)
You may run into trouble using this (COUNTIF has problems with the
used range in combination with this comparison).

To show this error try the following:
- open a new, frsh workbook
- enter the following formula in B1:
=COUNTIF(A1:A5;"<")

the expected result would be '0'. Though this generate for me the
result of '1'!


--
Regards
Frank Kabel
Frankfurt, Germany


Another less elegant response. Frank's answer is much more
appropriate.

=COUNTIF(H1:J5,"<")

Essentially, count if the cell is not equal to nothing.

K


---
Message posted from http://www.ExcelForum.com/