Site icon ExcelNotes

How to Count Cells Starting a Text String

If you count the number of cells with numbers, please use the COUNT function. To count the number of cells that start with a text string, you can use the COUNTIF function.

Example: You are working on a database with the names in column A, and the salary in column B.

Formula 1: To count how many people have names starting with "Liam"

= Countif(A2:A8, "Liam*")

The wild card asterisk (*) stands for "all letters" after "Liam".

There are 0 people having names starting with "Liam".

Formula 2: To count how many people's names starting with "JA" and have 6 letters in total

= Countif(A2:A8, "JA????")

The wild card question mark (?) stands for "one letter". To have 6 letters and start with "JS", you need 4 question marks in the formula. While the wild card of an asterisk (*) represents all the letters, the question mark (?) represents only one letter.

One person's name starts with JA and 6 letters in total, "JACK T".

Note: The COUNTIF function

= COUNTIF(Range, "Text*"),
where the 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 the COUNTIFS function to meet two or more criteria.

Count Cells Start with a Text String

Exit mobile version