How to Count Cells that Contain Text Strings

How to Count Cells that Contain Text Strings

When you have a large data set with text strings, you may need to find or count data based on their text content. You may know to do this with numerical values, but also know that this can be done to ant cells that contain text strings.

Think that you want to find certain names in a name list and you are not searching for the complete name but only a part of it. For example you want to count names who has “Da” in their names.

How to Count Cells that Contain Text Strings

Let’s see how we can do this. We can use COUNTIF() function to do this.

The syntax of this function as follows:

= COUNTIF(range, criteria)

Range: is the cells range where you want to search for

Criteria: is the condition or the text string we are looking for

We can use =COUNTIF(A2:A20,"*Da*") to count name who has “Da” in their names.

Note that we have to use an asterisk (*) and "*" is a wildcard matching any number of characters.

How to Count Cells that Contain Text Strings

How to Count Cells that Contain Text Strings

You have to remember that this function is case insensitive. That means "*Da*" and "*da*" would give you the same result.

If you want to count text string with exact number of characters then use question mark (?) to represent one character. See below example:

=COUNTIF(A2:A19,"*ex?????") syntax counts text strings which has “ex” in the content and has only 5 characters after “ex”.

How to Count Cells that Contain Text Strings

It has counted only two items (see the highlighted cells).

How to Count Cells that Contain Text Strings

If you want to count cells containing an actual question mark or asterisk, type a tilde () before the ? or * character in the criteria. (e.g. =COUNTIF(range,"*?*")

How to Count Cells that Contain Text Strings

 

 

 

Related Trainings

Most Popular Movies

Most Popular Movies

Getting produced year of popular films of the IMDB from unformatted title information with year. Also calculating votes ...


Practice: Adding values that meet a single criteria - Part II

Practice: Adding values that meet a single criteria - Part II

Adding values that meet a single criteria by using SUMIF(), AVERAGEIF() and COUNTIF() functions.


Richest Business People

Richest Business People

Look at the some of richest people in the world. A table of these people age, earnings, the company owned by those compa...