PDA

View Full Version : [SOLVED] Count functions, mostly at COUNTIF() Question



GTO
06-13-2014, 10:04 PM
Hi All :hi:

A hopefully easy/quick question, or more at confirmation...

In the Help topic for COUNTIF() in 2010 Help, in the example(s) area, it is stated:

=COUNTIF(A2:A7,"*") Number of cells containing any text in cells A2 through A7.

I believe this is incorrect, as if I have:

=COUNTIF(B$136:B$155,"*")

... and B$136:B$155 contain formulas that either return a string (employees' names in this case), or, return an "" (empty string), then even the cells returning empty strings are added/counted; just like if I were to use COUNTA().

If I add a question mark (insist upon at least one character) however:

=COUNTIF(B$136:B$155,"?*")

...then it appears to return the count of cells containing text, as is desired.

To test and hopefully confirm my assumption that the '*' is being treated as one would expect (zero to many characters), I essentially repeated by testing on a blank/new sheet. With 'Amy', 'Lisa' and nothing in A1:A3, and the formula =IF(LEN(A1)>0,A1,"") in B1 and drug down, the formulas =COUNTIF(A1:A3,"*") and = COUNTIF(B1:B3,"*") returned 2 and 3 respectively. Again, if I added the question mark, then both formulas accurately reported '2' accurately.

So, I thought I'd pose this question to make sure that the help topic's example is in error, just to ensure that I'm not presuming anything incorrectly and actually missing something...


Thank you in advance,

Mark

p45cal
06-14-2014, 04:21 AM
Either it is an error or it's treating any cell which has a formula which returns a string type value as text, regardless of whether it's zero-length or not. The * wildcard usually includes zero-length strings.
While playing with this I noticed cell values which were both of Typename (vba) "String" and formula =Cell("Type",~) returned l for label included:
cells with just an apostrophe
cells with the formula =""
cells with the formula =Left(A2,0) regardless of whether A2 was blank or not.

All the above cells counted towards the result of a =Countif(~,"*")
Empty cells formatted as Text didn't.

Paul_Hossler
06-14-2014, 06:14 AM
... and B$136:B$155 contain formulas that either return a string (employees' names in this case), or, return an "" (empty string), then even the cells returning empty strings are added/counted; just like if I were to use COUNTA().


An empty string has Len = 0, but is still a string. As a test, clear the "" cells and see if COUNT changes

GTO
06-15-2014, 04:16 AM
Hi Pascal and Paul,

Thank you both for your testing and observations. (@ Pascal's comments) I do not think it's an "error" as such, but rather, as you mentioned, if IF (or whatever) returns an empty string, the cell's Value is "sub-typed" to a string. I did not think of checking with TypeName, and am sure I would not have thought to check with CELL(). Nicely done and thank you so much!

I think I am following what both of you are saying, and in particular, I found it interesting that an apostrophe marks the cell as text. (See attached, I tried and duplicate Pascal's testing)

Well, I will leave the thread unsolved for a day or two, just to see if any more observations are forthcoming, but in short, I think the Help example is just poorly written. It's also a tad confusing for yours truly here, that COUNTBLANK() counts cells with empty strings as BLANK, yet COUNTA() also counts them...

Anyways COUNTIF([Range],"?*") seems to overcome this, so hopefully I remember that in the future...

Thanks again,

Mark

Paul_Hossler
06-15-2014, 06:09 AM
http://office.microsoft.com/en-us/excel-help/countif-function-HP010342346.aspx

Example 2, Row 11 seems to be your question

Unfortunately, the 'blank' rows in the example are most probably truly Empty, and do not contain a zero length string so I think that why it 'works' that way

I've messed up many a macro by not handling zero len strings

Trip down memory lane -- We used to have a lot of Lotus 1-2-3 users who would just type a space in a cell to 'clear it': "It looks blank, so it must be empty"

That was a hard habit to break when we moved to Excel


You don't have 'Transistion Navigation Keys' in Options, Advanced, Lotus Compatibiliy checked I'm sure but I think that's where the leading apostrophe = treat as Text comes from


http://office.microsoft.com/en-us/excel-help/lotus-1-2-3-keyboard-equivalents-HP005201158.aspx


Use the following text-alignment prefix characters to assign alignment formats as you enter data in cells.
Press To
' (apostrophe) Left align data in the cell
" (quotation mark) Right align data in the cell
^ (caret) Center data within the cell
\ (backslash) Repeat characters across the cell

GTO
06-17-2014, 04:11 AM
Hi Paul,

Thank you for the nice links. I doubt I started using a PC as long ago as you, but I seem to recall that Lotus 1-2-3 also started formulas without the equals sign. Gosh that's a while ago, and I was such a rudimentary user. Then we went to QuatroPro (which I think I'm missing a "t" in maybe). It was a while before we switched to MS Office. I still miss 'Reveal Codes' in WordPerfect (which is what I wrote my first little bits of code in).

Anyways, thank you both again :beerchug:,

Mark