Excel Formulas - Count number of cells containing any text

Count number of cells containing text (exclude only number, blank, or error)

Syntax of used function(s)

COUNTIF(criteria_range, criteria)

The COUNTIF function is used to count the number of cells that meet a certain criteria.

Explanation

To count number of cells within a range of cells which contain any text except number, blank, or error the COUNTIF function can be used.

Formula

=COUNTIF(Sample_Text,"*")

How this formula works

In this formula the COUNTIF function search each cell in the range whether it contain any text except number, blank, or error and count if met the criteria.

Count number of cells containing no text (excluding text )

Formula

=COUNTIF(Sample_Text,"<>*")

How this formula works

In this formula the COUNTIF function search each cell in the range and ignore counting if contain any text (except number, and error).

Count number of cells containing text with no blank cell

Syntax of used function(s)

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met

Formula

=COUNTIFS(Sample_Text,"*",Sample_Text,"<> ")

How this formula works

In this formula the COUNTIFS function search each cell in the range and ignore counting if contain any blank cells.

Count number of cells containing only text using SUMPRODUCT()

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
ISTEXT(value)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products. The ISTEXT funtion returns True if the value refers to a text.

Formula

=SUMPRODUCT(--ISTEXT(Sample_Text))

How this formula works

In the above formula the ISTEXT function search within the range Sample_Text and returns true is text found and creates an array like -

{TRUE;TRUE;FALSE;TURE;FALSE;FALSE;TRUE;} 

Threfore the double negative (--) sign converts it like -

{1;1;0;1;0;0;1;} 

and SUMPRODUCT then counts and return result.

Previous: Excel Formulas - Count number of cells containing specific text
Next: Excel Formulas - Count number of rows for a specific matching value