Counting text 2

[FacilitiesCAD]

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.

ex:


A B
1 Name1 5
2 Name2 17
3
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.
Thanks

Tim

 

[onlyadrafter]

Hello,

How about this?

=SUMPRODUCT(--(LEN(E1:E39)>0))



----------------------------------
Hope this helps.
----------------------------------

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

 

[FacilitiesCAD]

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

As promised a star

Tim

 

[onlyadrafter]

Hello,

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

http://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!

 

[TriumphTR6]

How about =counta(a1:a5)

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

 

[FacilitiesCAD]

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.

Tim

 

[electricpete]

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.

thx.

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

 

[aviat]

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.