counting no of "text" cells

[Silvabod]

Apols, I've tried a number of things but cannot find the answer, all refer
to numeric values, which this isn't.

Have a column of 70 text cells (I've range-named it "names")

I need to count the number of names, not at the bottom of the column, in a
different location..
Some cells are blank, (actually "0", with the "0" hidden). End use is as a
dynamic (changing) number in a calculation.
What I'm getting (on 58 names) is 12 (which is the number of "0" or blank
cells).

Function to count the number of cells with text, please?

 

[Dave Peterson]

=SUMPRODUCT(--(ISTEXT(names)))
or
=SUMPRODUCT(--(ISTEXT(D574)))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Silvabod]

Thanks, Dave, option 1 worked!
Appreciate the "help" links, I lost all mine when I gave my old pc to my
daughter (far away)
Saved the OE wab and message files, forgot the ng files!!

Retired, only an occasional Excel user now - this bit of code completes a
comprehensive "Lotto" s/sht for my "local"!
Looks simple (just a report list of names and numbers) but there's about
2000 bits of code/condtional formatting in it!
If you want it (for your local) I'll give it to you, warts and all (all code
works).

 

[Dave Peterson]

Thanks for the offer, but I don't think I need it.

Good luck.

 

[Harlan Grove]

Dave Peterson wrote...

=SUMPRODUCT(--(ISTEXT(names)))
or
=SUMPRODUCT(--(ISTEXT(D574)))

....

FTHOI, it can be done with a single function call.

=COUNTIF(D574,"*")