COUNTIF Cell That Contains a Specific Text in Excel

Quick View to COUNTIF Cell Contains Specific Text

Today I will be showing how you can count cells that contain a specific text within it. I will show both case-insensitive and sensitive ways to execute this.

Countif Cell That Contains a Specific Text in Excel (Quick View)

Quick View to COUNTIF Cell Contains Specific Text

Download Practice Workbook

Countif Cell That Contains a Specific Text in Excel

Here we’ve got a data set with the Names and Authors of some books of a bookshop named Kingfisher Bookstore.

Data Set to COUNTIF Cell Contains Specific Text

Our objective today is to count the number of books with some specific texts in the names.

1. Using Asterisk Symbol (Case-Insensitive Match)

First of all, let’s try to count the number of books on World War.

That is, we shall count the number of books having the text “World War” in the name.

We can use the COUNTIF function of Excel with the Asterisk (*) symbol for this purpose.

The formula will be:

=COUNTIF(B4:B22,"*World War*")

Asterisk Symbol to COUNTIF Cell Contains Specific Text

Here we’ve used the Asterisk (*) symbol on both sides of the text “World War”.

Because we assumed that there may be unknown characters in both sides of the text “World War” in the names.

To count the number with unknown characters only on one side, use the Asterisk (*) symbol only on that side.

For example, to count the number of books beginning with the text “Harry Potter”, the formula will be:

=COUNTIF(B4:B22,"Harry Potter*")

Asterisk Symbol to COUNTIF Cell Contains Specific Text

Here we’ve used the Asterisk (*) symbol only in the end.

Similarly, to count the number of books ending with the text “Freedom”, the formula will be:

=COUNTIF(B4:B22,"*Freedom") 

Asterisk Symbol to COUNTIF Cell Contains Specific Text

Here we’ve used the Asterisk (*) symbol only in the beginning.

Finally, you can also use cell references with the Asterisk (*) symbol. Just join them into a single text using the Ampersand (&) symbol.

For example, to count the number of books with the text “World War”, you can also use:

=COUNTIF(B4:B22,"*"&E4&"*")

Asterisk Symbol to COUNTIF Cell Contains Specific Text

Or to count the number of books starting with the text “Harry Potter”, you can use this formula:

=COUNTIF(B4:B22,E5&"*")

Asterisk Symbol to COUNTIF Cell Contains Specific Text

Keep in Mind

The COUNTIF function works with case-insensitive matches.

That means COUNTIF(B4:B22,"harry potter*") in place of COUNTIF(B4:B22,"Harry Potter*") will also work.

2. Using Question Mark (Case-Insensitive Match)

You can use the Question (?) Mark in place of the Asterisk (*) symbol when you know exactly how many unknown characters are there.

Here we’ve another data set with the Names of some employees and their Home Countries of a company called Mars Group.

Data Set for COUNTIF Cell Contains Specific Text

Now, if someone wants to know how many employees are there from Korea, then? What to do?

There are two types of Korea: North Korea and South Korea.

Obviously, you can use the COUNTIF function with the Asterisk (*) symbol here.

But as both the Koreas (North and South) have the same number of characters (6 including the space) before the term “Korea”, you can also use the Question Mark (?) here.

The formula with the COUNTIF function and the Question Mark will be:

=COUNTIF(C4:C22,"??????Korea")

Question Mark to COUNTIF Cell Contains Specific Text

Here we’ve used six Question Marks (??????) before the term “Korea” because there were exactly six unknown characters before the term.

This method is quite useful when you have to count the number of texts having a specific number of characters.

For example, to count the number of employees with exactly 11 characters in the name, you can use this formula:

=COUNTIF(B4:B22,"???????????")

Question Mark to COUNTIF Cell Contains Specific Text

We have used 11 Question Marks here because we are looking for 11 unknown characters.

And sure, you can use cell references with the Question Marks. Join them into a single text using the Ampersand (&) symbol.

For example, to count the number of employees from Korea, you can also use this formula:

=COUNTIF(C4:C22,"??????"&E4)

Question Mark to COUNTIF Cell Contains Specific Text

3. Using SUMPRODUCT Function (Case-Sensitive Match)

Until now we used only the COUNTIF function. But you can also use the SUMPRODUCT function of Excel to perform the same task.

We come back to our original data set, the data set of the book records of Kingfisher Bookstore.

To find out the number of books with the text “World War”, you can also use this formula:

=SUMPRODUCT(--ISNUMBER(FIND("World War",B4:B22)))

SUMPRODUCT Formula to COUNTIF Cell Contains Specific Text

Explanation of the Formula

  • FIND("World War",B4:B22) goes through each cell of the range B4 to B22 and searches for the text “World War” within them.

Returns the matching position in the cell if finds a match, and returns #VALUE! error if does not find a match. See the FIND function for details.

FIND Function to COUNTIF Cell Contains Specific Text

  • ISNUMBER(FIND(“World War”,B4:B22)) converts the numbers into TRUE and everything else into FALSE.

FIND Function to COUNTIF Cell Contains Specific Text

  • –ISNUMBER(FIND(“World War”,B4:B22)) converts the TRUES into 1’s and the FALSES into 0’s. See the ISNUMBER function for details.

FIND Function to COUNTIF Cell Contains Specific Text

  • Finally, SUMPRODUCT(–ISNUMBER(FIND(“World War”,B4:B22))) returns the sum of the total array.

This is the required number of books having “World War” in the names.

Keep in Mind

The FIND function is case-sensitive.

So SUMPRODUCT(--ISNUMBER(FIND("world war",B4:B22))) in place of SUMPRODUCT(--ISNUMBER(FIND("World War",B4:B22))) will not work.

Conclusion

Using these methods, we can count the total number of cells containing any specific text within it. Do you know any other method? Or Do you have any questions? Feel free to ask us.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo