Countif using Criteria "<>""" ?

  • Thread starter Thread starter JMay
  • Start date Start date
J

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,,,
 
It's not reliable using it this way

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

or

=COUNTIF(B5:B20,"<>"&"")
 
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
 
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
 
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 : /
 
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
 
RagDyer said:
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.
Click to expand...

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.
Click to expand...

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
 
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.
Click to expand...
...
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.
Click to expand...

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

Alan Beban
 
Thanks,
Tom Ogilvy


Alan Beban said:
...
..
Harlan Grove wrote in response:

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

Alan Beban

Tom Ogilvy wrote:
Click to expand...
 
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.
 
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
 
Back
Top