How to Count Cells that Contain a Text String

To count the number of cells that contain a text string, you can use the COUNTIF function.

Formula:

= COUNTIF(Range, "*Text*")
where asterisk (*) stands for all other letters.

Explanations:

– The range is required, which is the range of cells that you want to count;

– "*Text*" is required, a text string to look for; an asterisk (*) is the wild card for all other letters.

Cautions:

The COUNTIF function can count the range of cells with a single criterion. Please refer to the COUNTIFS function when you have 2 or more criteria.

Wild card asterisk (*) stands for "all other letters", while Question Mark (?) stands for "one letter".

Example 1: To count how many people having the letter "min" in their names

– Range: Column A (A2:A8)

– "*Text*" (criteria): Contain "min" with or without other letters, "*min*".

= Countif(A2:A8, "*min*")
There are 2 people having the first names with "min".

Example 2: to count first names that contain letters "U" with one letter in front.

= Countif(A2:A8, "?U*")
There is 0 persons name contains the letter "U" and one letter in front.

Example 3: to count names that contain letters "th" with 2 letters at the end.

= COUNTIF(A2:A8, "*th??")
There is 0 person's first name contains letters "th" with 2 letters at the end.

Count Cells Contain a Text String

Leave a Reply