Countif using cells formatted as text

R

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

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*****
 
R

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!)
 
D

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))
 
Top