Count words / text

[mgirvin]

Dear Most Amazing Excel Team,

If I have numbers, words, errors, blanks and True/False values in the range C2:C9, and I want the shortest (in length) and fastest (calculation time) formula for counting just words, what formula should I use?

I have created this one which works:

=SUM(IF(ISTEXT(C2:C9),IF(C2:C9<>"",1,0)))

Entered with Ctrl = Shift + Enter

Any ideas on better formulas (shorter, faster)?

[Colin Legg]

I want the shortest (in length) and fastest (calculation time) formula for counting just words

ISTEXT ignores blank cells so this should work?

=-SUMPRODUCT(-ISTEXT(C2:C9))

[mgirvin]

Dear Colin_L,

My fault here, I forgot to say that I have formulas in the range that evaluate to blank and so the SUMPRODUCT formula would count those.

I formulas like:

=""

and

=IF(A3=1,3,"")

So the revised list of what could be in the range C2:C9 is:

1) Words / Text
2) Numbers
3) Errors
4) Blanks
5) Formulas that evaluate to blanks
6) TRUE / FALSE

Thanks!

[scottylad2]

Colin thats fantastic........clever yet so simple!

[Colin Legg]

Maybe to allow for errors and formulas that return "", this would be an option?

=SUM((LEN(IF(ISERROR(C2:C9),"",C2:C9))>0)*ISTEXT(C2:C9))

CTRL+SHIFT+ENTER, not just enter.

[Aladin Akyurek]

Try...

=COUNTIF(C2:C9,"?*")

[Colin Legg]

Originally Posted by Aladin Akyurek

Try...

=COUNTIF(C2:C9,"?*")

Much better!

[mgirvin]

Yes, This is the one:

=COUNTIF(C2:C9,"?*")

Thanks Aladin!

I am very happy to now add "?*" to my bag of Excel Tricks for the times when I want to count words when there may be blanks from formulas.

Thanks everyone!

This is such a great Excel Team!