Other than counting the number of cells that start or end with a certain text string, you can also count the number of cells that contain a certain text string with the COUNTIF function. The text string can be in any position in the cell.
Example: You are working with a dataset with the names in column A and the salaries in column B. You need to count the number of employees based on certain rules.
Formula 1: How many people have the letters "min" in their names?
= Countif(A2:A8, "*min*")
The wild card asterisk (*) stands for "all other letters" before or after the text "min".
The result returns 2, so two people's names include "min": Benjamin MOORE and Jacob MINON.
Formula 2: How many people's names contain the letter "U" with one letter in front?
= Countif(A2:A8, "?U*")
The formula uses the asterisk (*) and the question mark (?). While the asterisk stands for all letters, the question mark (?) stands for "one letter".
The result returns 0, and no people's names with the letter "U" and one letter in front.
Formula 3: How many people's names contain the letter "th" with two letters at the end?
= COUNTIF(A2:A8, "*th??")
The result returns 0, and no people's names have the letters "th" and two more letters after.
Notes: The COUNTIF function
Formula:
= COUNTIF(Range, "*Text*")
where the 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.
Practice: Count Cells Contain a Text String