Excel Formulas - Count number of cells containing specific text

Count number of cells containing a specific text at any position

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 the number of cells in a range of cells which containing the even numbers, we can use the SUMPRODUCT function along with the MOD function.

Formula

=COUNTIF(Invoice_No,"*w*")

How this formula works

In the above example a range name "Invoice_No" have been definded instead of a range. The COUNTIF function counts the cells where the letter "w" exists in the text.

Count no. of cells containing specific text using criteria range

Explanation

Counts number of cells containing "w" at any position in the text using criteria variable.

Formula

=COUNTIF(Invoice_No,D7)

How this formula works

In the above example a range name "Invoice_No" have been definded instead of a range. The only difference with the previous is a criteria variable have been used.

Count no. of cells containing specific text using criteria variable

Formula

=COUNTIF(Invoice_No,"*"&D7&"*")

How this formula works

In the above example range name and criteria variable have been used. To understand "w" at any position of the text the wildcard character "*" have been used. The "*" and the criteria variable D7 have been concatinated by & operator.

Count no. of cells containing specific text ( case sensitive ) using criteria variable

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
ISNUMBER(value)
FIND(find_text, within_text, [start_num])

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.
The ISNUMBER funtion returns True if the value refers to a number.
The FIND function is used to locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

Formula

=SUMPRODUCT(--(ISNUMBER(FIND(D7,Employee))))

How this formula works

Inside the formula the ISNUMBER(FIND(D7,Employee)) works as -

the FIND function returns the number of the position where the text (written in variable D7 ) found and ISNUMBER function converts it into True or False and make an array like -

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

Therefore the double negative (--) sign will convert the True and False in 1 and 0's and at last the SUMPRODUCT will give the result.

Count no. of cells, does not contain specific text

Explanation

To count the number of cells does not contain the letter "w" at any position of a text from a range of cells, the COUNTIF function can be used.

Formula

=COUNTIF(Invoice_No,"<>*w*")

How this formula works

In the above example a range name "Invoice_No" have been definded instead of a range. The COUNTIF function counts the cells where the letter "w" does not exists in the text.

Count no. of cells, does not contain specific text excluding blank

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

Explanation

To count the number of cells( ignore blank cell ) does not contain the letter "w" at any position of a text from a range of cells, the COUNTIF function can be used.

Formula

=COUNTIFS(Invoice_No,"<>*w*",Invoice_No,"?*")

How this formula works

In the above example the COUNTIFS function counts the cells that does not contain the "w" at any position in the range "Invoice_No" and met another criteria that, the cell does not contain any character.

Previous: Excel Formulas - Count number of cells containing even or odd numbers
Next: Excel Formulas - Count number of cells containing any text