Counting Cells With Text

[DonArturo]

I would like to be able to count the number of cells within a range that
contain text, excluding those that are strictly numeric values. For example,
counting:
34
52
n/a
987
ooops
4
would return the result of 2.

 

[Glenn]

I would like to be able to count the number of cells within a range that
contain text, excluding those that are strictly numeric values. For example,
counting:
34
52
n/a
987
ooops
4
would return the result of 2.

Array formula (commit with CTRL+SHIFT+ENTER):

=SUM(--ISTEXT(A1:A6))

Keep in mind that this will also count cells that look like numbers but are
actually entered as text. As an example, enter '52 instead of 52 in the data above.

 

[Mike H]

Hi,

Try this

=COUNTA(A1:A6)-COUNT(A1:A6)

Mike

 

[Harlan Grove]

Try this

=COUNTA(A1:A6)-COUNT(A1:A6)

....

This counts all entries except numbers, which means it'd count boolean
and error values in addition to text.

Simplest way to count cells containing text is

=COUNTIF(range,"*")

which would include nonblank cells evaluating to "". To count only
text with one or more characters, use

=COUNTIF(range,"?*")

 

[DonArturo]

Thanks guys. All three solutions worked as you said.
Harlan: Using =COUNTIF(range,"*") was especially helpful because it counted
both the text cells and the "" or null cells, which is what I needed. That
is, it counted them in the simple COUNTIF version. Using COUNTIFS, however,
produced a different result because I must put the equals inside the quotes.
Here is the actual formula:
=COUNTIFS($D$2:$D$1600,AE1,$K$2:$K$1600,">0",$N$2:$N$1600,"=*")
In this instance, it only counts the text cells and not the "" or null
cells. My workaround is to also use
=COUNTIFS($D$2:$D$1600,AE1,$K$2:$K$1600,">0",$N$2:$N$1600,"")
and add them together.
Unless you know a better way...
Thanks again.
Peace


_________________________________________