Countif using Criteria "<>""" ?

[JMay]

In cells B5:B10 I have numbers,
Cells B11:B20 are blank (I have selected and pressed delete)

So Why does
=COUNTIF(B5:B20,"<>""") In D4

give me 16?

TIA,,,

 

[JohnI in Brisbane]

JMay,

The following seems to work-

=COUNTIF(B5:B20,"<>")

regards,

JohnI

 

[Peo Sjoblom]

It's not reliable using it this way

=SUMPRODUCT(--(B5:B20<>""))

or

=COUNTIF(B5:B20,"<>"&"")

 

[Alan Beban]

Or COUNTIF(B5:B20,"<>"&"") or COUNTA(B5:B20).

Given that the standard syntax for COUNTIF with a comparison operator
is, e.g., COUNTIF(B5:B20,"<>SOMETHING"), the formula you posted is
counting all cells within the range that do not contain the empty cell,
i.e., ="", and none of them does.

Alan Beban

 

[Alan Beban]

On a little further testing it turns out that your original formula is
not counting all cells without the empty string; it seems to be counting
all cells whose value is not "

Alan Beban

 

[hickman]

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 : /

 

[Alan Beban]

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

 

[Alan Beban]

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.

Not sure what you mean by "null". If you consider the empty string
(=""), ', Left("A",0) and such as "string blanks", and truly empty cells
as "true blanks", then =COUNTIF(rng,"=") returns (subject to the
exception for new worksheets) the number of true blanks, and
COUNTIF(rng,"") returns the number of true blanks and string blanks
combined.

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

I'm also not sure what you mean by "non-blank" in the above statement.
COUNTIF(rng,"*") returns the number of cells whose content is of the
String type, including string blanks.

Alan Beban

 

[Alan Beban]

There was a discussion of this recently in this group or ...programming
or ...misc, but after a brief Google search I couldn't find it. My
recollection is that were some pros and cons of the various methods,
with a conclusion that the -- avoided one or more problems of the other
methods. Maybe someone can point us to it. Harlan Grove and Aladin
Akyurek come to mind as having been involved in the discussion, but I
could well be wrong about that.

...
Harlan Grove wrote in response:

Efficiency could be an argument in favor of --, but not much of one. >It's
advantage is that it binds tightest because unary minus has the highest
precedence of any operator, even exponentiation. Therefore, it's much
less
susceptible to typos and other unintentional errors that occur in
formulas.

I still don't know where the discussion that I think I recall is.

Alan Beban

 

[Tom Ogilvy]

Thanks,
Tom Ogilvy

...
..
Harlan Grove wrote in response:

I still don't know where the discussion that I think I recall is.

Alan Beban

Tom Ogilvy wrote:

 

[Tom Ogilvy]

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.

 

[RagDyer]

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