Weird results from COUNTIF ?
Posted by Juan Pablo G. on January 14, 2002 9:14 AM
Hi,
I'm trying to solve the last entry using formulas, not VBA. We've used a lot here the
=MATCH(9.9999E+307,A:A)
to get the last numeric entry on column A, but the problem is if i'm looking for the last text entry, right ?
Well, i was experimenting with COUNTIF with this simple data. 2 in A2, 5 in A12 and "g" in A18. Everything else is empty, really empty (Do this on a blank sheet).
Now, if i use
=COUNTIF(A:A,"<>")
i get 3, as expected, only three data there. But if i use:
=COUNTIF(A:A,"<>""")
i get 18 ? all cells from A1:A18 that are not A2,A12 and A18 are empty, so why is COUNTIF counting them ? anyway, to get the last whatever (Numeric or text) entry on a column you could use:
=INDEX(A:A,COUNTIF(A:A,"<>"""))
Juan Pablo G.
Posted by Aladin Akyurek on January 14, 2002 10:23 AM
Juan --
I can't think of a logical explanation or one acceptable to intuition (compared to MATCH).
One observation:
=COUNTIF(A:A,"<>""") counts the last cell that is blank returned by a formula.
=INDEX(A:A,COUNTIF(A:A,"<>""") returns then a blank, as long as that cell is not cleared up hitting delete key. If the latter happens, INDEX returns zero while COUNTIF still counts that cleared-up cell.
If "this behavior" of COUNTIF is also observable in earlier versions of Excel, I'd say we have here a single method to get the row/column index or the value of the last used cell in a column or in a row (it works on rows too).
A side note. I used COUNTIF(A:A,"?*") often in defining a dynamic range instead of the volatile COUNTA. It didn't occur to me to hack on COUNTIF(A:A,"<>"""). "Serendipity" is a nice thing to have. Kudo's Juan.
Aladin
==========
Posted by Jack in UK on January 14, 2002 11:01 AM
Hi Juan--
I side with Aladins formula aswell as your own but..
"cleared up hitting delete key" as Aladin points out is critical, i get everyday people in my office messing with cells and formatting this is common, but you point out MUST BE ON NEW SHEET ie CLEAN.????
So you say input in 3 randon cells in ColA NOT touching the others but Countif returns 18??? Odd that.
All i would try just in sake is to filetr and list blanks highlight and right click, clear contence just in case, i know it wont work, but im lost, ill spend time on this one and post on this feed if i have news.
Juan - you have a good one there!
Jack in UK
Posted by Mark W. on January 14, 2002 11:24 AM
Juan Pablo G., "<>" means "does not equal nothing
(null)" or after removing the double negative
"equals (or contains) something". Keep in mind
that the empty string is is indeed "something" so
"<>""" means "does not equal the empty string".
Since none of the cells in A1:A18 contain ""
COUNTIF(A1:A18,"<>""") returns 18. I've found
that the criteria used by COUNTIF and SUMIF behave
very much like the "old" Dfunction criteria.
In fact if you insert a new row 1:1, enter
"Field1" into A1 and C1, enter <>"" into
C2 and perform an Advanced Filter specifying
C1:C2 and the Criteria you'll get the same
results. Now enter <> into C2. Re-do the
Advanced Filter... Voila, 3 filtered rows.
Posted by Juan Pablo G. on January 14, 2002 11:42 AM
Hi Mark,
I see your point, but now i have another question, regarding your answer. Since i created the function to count in A:A, does all functions restrict them selves to the Used Range of the sheet ?
Because i couldn't figure out another way of getting the 18, meaning, if i count all cells that "don't equal the empty string" in column A, i should get 65536, right ?
Thanks again.
Juan Pablo G. , "<>" means "does not equal nothing
Mark makes a good point of the difference between <>"" and <> alone, and don't know how "far" behind are you talking about in Excel versions, but i did this in Excel 97.
Jack, yes, empty sheet, i didn't tried it on a "used" one, but i guess, from Aladin's comments, that it also works that way. (Should at least !)
Juan Pablo G.
Posted by Juan Pablo G. on January 14, 2002 11:45 AM
Posted by Aladin Akyurek on January 14, 2002 12:04 PM
Juan,
I know that difference, but it raises that "darn" question you posed to him, which also keeps appearing in my mind. "Used range" is probably invoked, one would think, when a function (at least some of them) has to "default something." As we know, functions ordinarily expects a user-given or computed range. Another side note, many people knew about giving a criterion like <>"" to Advanced Filter, but, as far as I know, this is not done with criteria accepting funcs like countif for the goal we're discussing now.
I've done the test on Office 2000 on Mac. I posed that version question in case something has been changed to the code implementing countif. If not, that's good news.
Aladin
========= ,
Posted by Juan Pablo G. on January 14, 2002 12:21 PM
Aladin,
I'm not trusting this method that much anymore. Still same scenario, formula in B1
=COUNTIF(A:A,"<>""")
if you put anything in any cell in column A, then the formula will recalculate and show the right result, now, put something in a row that's below the last used one in col A. The formula won't recalculate. Now, edit it and press enter without any changes, the formula will now show the last row. If you delete this last entered value, and again edit the formula, the value will be the same !
That leads me to the conclusion of the UsedRange of the sheet, because it's a known fact that Excel sucks when trying to keep track of the last cell.
So, maybe for another purpose, like finding the last *used* row/column in a sheet, but again, not very reliable (Just as reliable as the go to, special, last cell, which is not very reliable)
Juan Pablo G.
> if you put anything in any cell in column A, then the formula will recalculate and show the right result, now, put something in a row that's below the last used one in col A. The formula won't recalculate.
Now, edit it and press enter without any changes, the formula will now show the last row.
It does recalculate on my Mac (Off 2000).
> If you delete this last entered value, and again edit the formula, the value will be the same !
Right. This is a variant of the "glitch" I mentioned in my first reply. Checked just to make sure: MATCH is not affected by such changes.
> That leads me to the conclusion of the UsedRange of the sheet, because it's a known fact that Excel sucks when trying to keep track of the last cell.
Yep. I'd like some "official" confirmation here, when forced to "defaulting to something", some functions resorts to "UsedRange".
> So, maybe for another purpose, like finding the last *used* row/column in a sheet, but again, not very reliable (Just as reliable as the go to, special, last cell, which is not very reliable)
No. Delete actions occur too often (as Jack noted). What a pity.
Aladin
=======
