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)

**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.

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

**Related: How Do I Count Cells with Text in Excel**

**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

**will also work.**

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

**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)`

**Similar Readings:**

**Count If Cell Contains Text in Excel (5 Easy Approaches)****How to Count Specific Words in a Column in Excel (2 Methods)**

**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**

goes through each cell of the range`FIND("World War",B4:B22)`

**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

**will not work.**

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

**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.