COUNTIF for text and strings

[Eric Sabine]

I am trying to help a person who has Excel 2000. I have 2003.

They have a column with data like this

10
25
n/a
ns
26

they want a countif as follows

=countif( columns-range , "n/a")
=countif( columns-range , "n/a")
=countif( columns-range , "n/a")
=countif( columns-range , "n/a")

 

[JulieD]

Hi Eric

i don't have 2000 on this machine but i don't think the countif function has
changed so how about
=COUNTIF(A:A,"n/a")

Cheers
JulieD

 

[Eric Sabine]

Sorry for that last post. I think I hit ALT-S by accident.

I am trying to help a person who has Excel 2000. I have 2003.

They have a column with data like this

10
25
n/a
ns
26

they want a countif as follows

=countif( columns-range , "n/a")
=countif( columns-range , "ns")
=countif( columns-range , >25)
=countif( columns-range , >10)

but the problem is the data was imported from Access, so the 10, 25, and 26
are stored as text, not numbers. My excel shows that they are stored as
text and give me the little green triange on the cell to change it, but how
do I tell this person to change them? If I do CTRL-F1 on the cell and
change the type to number, it doesn't work. The count remains at 0 for the

25 and > 10 countif functions. The column contains over 1000 data points

which makes doing it manually too hard.

thanks
Eric

 

[JulieD]

Hi Eric

if they select a blank unused (maybe on a different sheet cell) and choose
copy
then select the column with the data in it and choose
edit / paste special - ADD
and click OK the numbers will return to numbers

however
=COUNTIF(A:A,">25")
should work even if the data is text

let us know how you go

Cheers
JulieD

 

[RagDyeR]

<"=COUNTIF(A:A,">25")
should work even if the data is text">

Are you sure about that Julie?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi Eric

if they select a blank unused (maybe on a different sheet cell) and choose
copy
then select the column with the data in it and choose
edit / paste special - ADD
and click OK the numbers will return to numbers

however
=COUNTIF(A:A,">25")
should work even if the data is text

let us know how you go

Cheers
JulieD

 

[JulieD]

Hi RD

odd, i have two ranges A:A & E:E ... A:A is the one i tested it on before
your post - here i entered numbers, and then changed the formatting to text
.... numbers went to left aligned so i assumed i had "text" .. and the
formula worked fine ...
E:E is the one i created after your post - formatted it as text, entered
numbers ... tested it - didn't work -
if i create another range .. F:F formatted as text & then copy A:A into it
the formula works
.... so i give up make of it what you will


Cheers
JulieD

 

[RagDyeR]

Looks can be deceiving.

ISTEXT and ISNUMBER are the true tests.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi RD

odd, i have two ranges A:A & E:E ... A:A is the one i tested it on before
your post - here i entered numbers, and then changed the formatting to text
.... numbers went to left aligned so i assumed i had "text" .. and the
formula worked fine ...
E:E is the one i created after your post - formatted it as text, entered
numbers ... tested it - didn't work -
if i create another range .. F:F formatted as text & then copy A:A into it
the formula works
.... so i give up make of it what you will


Cheers
JulieD

 

[JulieD]

Hi

yer, that would have been safer ...

Cheers
JulieD

 

[Eric Sabine]

Thanks, what then do I use if in column A if values are numeric but stored
as text? Can it be done?

I can't do something like =countif(cint(A:A),">25")

 

[RagDyer]

Yes you can !
*IF* you follow Julie's suggestion on how to convert the "text numbers" to
"real numbers".

<"if they select a blank unused (maybe on a different sheet cell) and choose
copy then select the column with the data in it and choose edit / paste
special - ADD and click OK the numbers will return to numbers">


--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks, what then do I use if in column A if values are numeric but stored
as text? Can it be done?

I can't do something like =countif(cint(A:A),">25")

 

[Eric Sabine]

OK I'll live with the work-around.

Thanks,
Eric

Yes you can !
*IF* you follow Julie's suggestion on how to convert the "text
numbers" to "real numbers".

<"if they select a blank unused (maybe on a different sheet cell) and
choose copy then select the column with the data in it and choose
edit / paste special - ADD and click OK the numbers will return to
numbers">

 

[Myrna Larson]

It will only work if the numbers are all the same length or are padded with
leading zeroes to the same length.

 

[Stuart]

This may be a little late for you; I've just seen your
post whilst searching for anold one of my own. You should
enter the number 1 in a blank cell, then copy it. Select
all your cells brought in from Access then paste special,
multiply.

That'll sort you out!

Stuart