Counting text
Counting text
(OP)
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
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
Talk To Other Members
RE: Counting text
How about this?
=SUMPRODUCT(--(LEN(E1:E39)>0))
----------------------------------
Hope this helps.
----------------------------------
maybe only a drafter
but the best user at this company!
RE: Counting text
As promised a star
Tim
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
A B
1 Name 1 5
2 Name2 17
3
4 Name3 0
5 Name4 10
4
RE: Counting text
Tim
RE: Counting text
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.
RE: Counting text
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.