Countif using Criteria "<>""" ?

Thanks Tom,
Never heard of "UsedRange" concept before.
(Lots'a things I've never heard of !)

But are there any other functions with similar properties?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Tom Ogilvy" wrote in message
...
Part of your finding is just the fact that Countif only works against the
UsedRange of the workbook. This is old hat. This can make counting blank
cells in a specified range using countif appear to return the wrong answer
if the UsedRange ends before the specified range ends. My understanding
is
Countif is based on the "D" or database functions (DSUM, DCOUNT,e tc) and
it would make sense to work on the usedrange.
--
Regards,
Tom Ogilvy



RagDyer wrote in message
...
In the same vein:

=COUNTIF(A5:A20,"=")

in a new WB returns 0.

Fill A5:A20, and then select and clear or delete, and you then get 16,
which
is correct if you consider "=" means equal to null.

Then, as you *refill* the cells, one at a time, the formula returns the
correct decremented values, one at a time.

Now, going a step further:

=COUNTIF(A5:A20,"*")

in a new WB or in used and/or reused cells works *all* the time.
Works for what ?
Returns non-blank, non-numeric (text) cells.

Did anyone realize this beforehand, or is this old hat ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"Alan Beban" wrote in message
...
It's even stranger still. Do the following:

In a new worksheet, enter in Cell a1 =COUNTIF(a5:a20,"""")
It will return 0.
Then in Cell A5 enter 1; the formula will now return 1.
Then in Cell A10 enter 1; the formula will now return 6!
Then select Cell A1 and fill to the right so that the formula in Cell B1
is =COUNTIF(b5:b20,""""); the formula in Cell B1 (recall that except
for the fill, nothing has been entered in Column B) will return 6!

I guess the lesson is don't use the syntax =COUNTIF(rng,"""")

Alan Beban

hickman wrote:
this seems to be some sort of bug in the software. it's
strange because if you perform this operation without
inputting (and then deleting) numbers into cells B10:B20
the formula works fine, and the number of non-blank cells
reads 6. however, if you input numbers into cells B10:B20
the formula gives 16, which is correct until you delete
the numbers (B10:B20). for some reason excel thinks that
there are still numbers in these cells.

why? i have no idea : /