Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count cells only containing text

Status
Not open for further replies.

FeS2

Technical User
Joined
Aug 16, 2002
Messages
82
Location
US
I have need to count the cells of a column that contains a list of names, none of the names are the same, it also contains a list of numbers. I would like to count only how many cells contain a text value, not a specific value just text. Is there a way to do that?
 
Try this function

=COUNTIF(A:A,"*")

might work

dyarwood
 
this should do the trick:
=SUMPRODUCT((ISTEXT(A1:A100))*(1))

where your data is in A1:A100

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Ok i tried both, it counted every cell. I figured out that all the cells a general format, could excel think they are all text then if they contain any value? This spreadsheet was generated from a report run against a huge database and has around 10000 rows. It would be inpractical for me to try and change the formating for the individual cells manually.
 
How about:
=COUNTA(A1:A100)-COUNT(A1:A100)

(again where data is in A1 - A100)
 
tried it, it counted every cell containing any data.
 
whew, thanks for the help, a coworker here had an idea that worked great. I added a new column and then put in this formula =IF((ISERROR(B10/2)), "x", (B10/55)). After that returns #value when it can't divide it puts an x in the cell and then i used a =COUNTIF(C:C, "x") to get the count I needed. Thanks for everyones help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top