Count words only

[Pigeon]

Dear Friends

If cell

A1 rat
A2
A3 cat
A4 bat

Now I want to count how many cells contain words.
but the thing is that cell A2 is not blank but contains a single space.
Please suggest a formula.


Regards
Pigeon
INDIA

 

[Aladin Akyurek]

1.

=SUM(COUNTIFS(A1:A4,{"?*"," "})*{1,-1})

This does not exclude a cell consisting of solely a bunch of spaces.

2.

=SUMPRODUCT((LEN(TRIM(A1:A4))>0)+0)

does so, including also any number,etc.

 

[admiral100]

Hi,

Another way : Ctrl+Shift+Enter NOT just Enter

=SUM((ISTEXT(A1:A4))*(A1:A4<>" "))