Counting cells with text?

[John Dubuc]

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]

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)

[#3]

Or

=COUNTIF(range,"?*")

which counts every cell with a least one character.