COUNTIF for text and strings

  • Thread starter Thread starter Eric Sabine
  • Start date Start date
E

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")
 
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
 
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
Click to expand...
which makes doing it manually too hard.

thanks
Eric
 
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
 
<"=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
 
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
 
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
 
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")
 
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")
 
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">
Click to expand...
 
It will only work if the numbers are all the same length or are padded with
leading zeroes to the same length.
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excluding a column from range in COUNTIF function 3
Excel Need Countifs Formula Help 0
Multiple arguments 1
countifs Function not giving desired result with date criteria 2
Finding the weighted average of text that have been given values 1
Formula Count dates multiple criteria 3
Countif gone wrong 2
countifs/or 2

Back
Top