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.
