Home

Counting cells with text?

Highlighted
Occasional Visitor

Greetings ALL,

 

How can I count the number of cells in a column that contain text when the cells contain a formula like vlookup which returns a text value if the value is found in a list and returns a blank otherwise?

 

COUNTIF(range,"*") always returns the number of cells in the range since each cell contains the vlookup function and so it sees the function as text.

 

So, is there a way to trick the COUNTIF function into counting the values in the cells and not the function text? Is there a better function(s) that will do this?

 

Much thanks!

 

2 Replies
Highlighted

Hi John,

 

I guess your formula returns "" if found nothing. Such cells are not blank, their value is empty string. To exclude cells with empty strings you may use something like

=SUMPRODUCT((A1:A100<>"")*1)
Highlighted

Or

 

=COUNTIF(range,"?*")

which counts every cell with a least one character.