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

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

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

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


Regional Sales Data

Regional Sales Data

Revisit your sales data, calculate regional totals, conditional totals


World Internet Users Statistics

World Internet Users Statistics

Create meaningful insights from internet users of top 25 countries between 2013-2015.