How to Count Cells that Start with a Text String

To count the number of cells that start with 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, the range of cells that you want to count;

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

Cautions:

The COUNTIF function can count the range of cells with a single criterion. Please refer to COUNTIFS function when 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 names start with "Liam"

– Range: Column A (A2:A8)

– "Text*" (criteria): Start with "Liam" with or without other following letters, "Liam*".

= Countif(A2:A8, "Liam*")
There are 0 persons people having the names start with "Liam".

Example 2: To count how many people's names that start with "JA" and have 6 letters in total

– Range: column A (A2:A8)

– "TEXT???" (criteria): cells start with "JA" and 6 letters in total, that is "JA????"

= Countif(A2:A8, "JA????")
One person's name starts with JA and 6 letters in total, "JACK S".

Count Cells Start with a Text String

Leave a Reply