Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Counting text

Counting text

Counting text

A friend stopped by with a question earlier and I found him a quick answer but I'm wondering if there is a better answer.

He had a list of text in some excel cells and he wanted to know how  many entries were in the list.


    A        B
1 Name1      5
2 Name2      17
4 Name3      0
5 Name4      10

so in cell a6 he wanted something like =count(a1:a5) but that gives an answer of 0.  He was able to use =count(b1:b5) in this case but I was wondering if there was a diferent way.


RE: Counting text


How about this?


Hope this helps.

maybe only a drafter
but the best user at this company!

RE: Counting text

Looks like it would do something.  I understand the LEN()>0 part but what is the -- for?

As promised a star


RE: Counting text


SumProduct needs numbers, while the conditional LEN(Range)>0 results, upon evaluation, in an array of logical values like {TRUE,FALSE,TRUE,...}. The -- bit before the conditional coerces the logical values to Excel's numeric equivalents, that is, TRUE --> 1 and FALSE --> 0.

May I suggest you visit www.mrexcel.com another forum based web site with many excellent Excel users, one of whom came up with this.

Hope this helps.

maybe only a drafter
but the best user at this company!

RE: Counting text

How about =counta(a1:a5)

    A    B
1    Name 1    5
2    Name2    17
4    Name3    0
5    Name4    10

RE: Counting text

Thanks both of you.  counta was the solution I was looking for but it is always nice to know more than one way to skin a cat.


RE: Counting text

Nice solution onlyadrafter.  I saw some of your great posts on that mrexcel.com as well.  

In my book, you are not onlyadrafter but an excel guru as well.


Eng-tips forums: The best place on the web for engineering discussions.

RE: Counting text

If you want to count the number of rows that contain text only use =sum(if(istext(A1:A5),1)) and enter as array formula (Ctrl+Shift+Enter).
This formula can be used to find number of rows that are blank,contains errors, have numbers, no text etc by using appropate "is" function.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close