Countif using cells formatted as text

[RWN]

Must be getting tired!

I imported a column of values as text.
All are 4 characters (year and month "9912", "9911" etc.).

Trying to use a "CountIf" function where, for example, I want the count
of items greater than "9501".
I let Xl tell me how it wants the formula, i.e. I accept the correction
to how I entered the conditional and it says;
=COUNTIF(F2:F266,">""9501""")
It returns a count of 265 (wrong).
"=" works but anything else fails.

I checked the help but nothing gives me a hint.

 

[Dave Hawley]

Hi Rob

Copy an emty cell. Select your text numbers, then paste special - values
- add. This will convert them to true numbers. If you wish to retain
leading zeros, use a custom format like "0000" Then simply use

=COUNTIF(F2:F266,">9501")

*****Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum & Business Software*****

 

[RWN]

Thanks Dave,
I knew I could do it this way, but does this mean XL cannot recognize a
greater/less than condition using text in a function like CountIf (or
SumIf)?

(I guess the answer is "No" because I can't get either of them to work!)

 

[Dave Peterson]

Since you're working with dates, you may want to be a little careful (in case
you hit 2000).

You may find that some of your cells look like: 0012, 0111, 0307

and those could be a problem.

I think I'd byte <bg> the bullet and use a helper column and convert those
strings to real dates:

=DATE(IF(LEFT(F2,1)>"3",1900,2000)+LEFT(F2,2),MID(F2,3,2),1)

(I used 1930 (3001) as my cut off for 1900 to 2000.)

Then I could use a different =countif() formula:

=COUNTIF(G2:G266,">="&DATE(1995,1,1))

 

[RWN]

Thanks Dave.
I'm conversant with the date problem and I only used it as an example
(albeit a real life one).

All I'm looking for is confirmation that my assumptions about the
Countif/Sumif functions are accurate i.e. cannot use ">" or "<"
comparators with a text field, only "=" . The Help on the functions
didn't say that I couldn't (which is not the same as saying I can!).


Again, thanks for your response.

 

[Dave Peterson]

You can, but you have to know how your data sorts.

=COUNTIF(B2:B5,">m")

works ok for me.

Try it with A-Z in A1:A26 and see if you get what you want/expect.

 

[RWN]

That works for me but (there's always a but)
First - I understand the collating sequence (although I always have to
remind myself where I am, was "brought up" on EBCDIC and flip/flop
between them regularly - hence the initial question on a file I
downloaded from the mainframe).

Using an example - six rows values 1->6 in Col C.
If I *format C1->C6 as text* and then enter the digits the function
fails (or rather, it works as intended and the problem is my lack of
understanding!).
I can use a helper column ("D") and copy =IF(Cr>"4",1,0) - xl has no
problem with it - i.e. SUM(D1,D6) returns 2
but if I say =COUNTIF(C1:C7,">4") it returns zero.

I know that xl (or the user) can get confused with digits (because, I
assume, of the sign byte) and that's why I formatted the column as text.

Again, thanks for your time on what will probably be a simple
misunderstanding on my part.

 

[Dave Peterson]

I agree with you. You have to know your data (and text numbers and number
numbers give excel (or me) a pain!)

But I could do this to return 2:

=COUNTIF(C1:C6,">4*")

It's a little cheating since it now returns 0 if those number's are really
numbers.

 

[RWN]

Hmmm, looks like I'll advise our users not to use the function, or, at
least, be very careful when using it if "text numbers" are involved.

Overall I've found XL to be the "greatest thing since sliced bread" with
my only disappointments being where they provide functions that allow
you to "hurt" yourself.
I've encountered situations where users have arrived at the wrong
conclusions using things like the Autofilter (didn't know about the
1,000 unique items limit), or sorting a sheet where there was a blank
row/column imbedded.
This, to me, is another one (two, if I include the SumIf) of those
instances.
It would be logical to include a "disclaimer" in the Help file
explaining the limitations.

However, you take the good with the bad, and, with XL, the good
outweighs the bad AFAIAC.

Thanks for the time (now go solve some real problems)