# COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive) Today I will be showing how you can use COUNTIF to count cell that contains 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) ## 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. 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*")` 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*")` 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") ` 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&"*")` Or to count the number of books starting with the text “Harry Potter”, you can use this formula:

`=COUNTIF(B4:B22,E5&"*")` 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. 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")` 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,"???????????")` 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)` ### 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)))` 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. • ISNUMBER(FIND(“World War”,B4:B22)) converts the numbers into TRUE and everything else into FALSE. • –ISNUMBER(FIND(“World War”,B4:B22)) converts the TRUES into 1’s and the FALSES into 0’s. See the ISNUMBER function for details. • 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.  