Re: Countif using cells formatted as text

From: Dave Peterson (ec35720_at_msn.com)
Date: 03/02/04 

Date: Tue, 02 Mar 2004 17:04:52 -0600

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 wrote:
>
> 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.
> --
> Regards;
> Rob
> ------------------------------------------------------------------------
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:4043F530.A226062@msn.com...
> > 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.
> > 

-- 
Dave Peterson
ec35720@msn.com