How to Apply COUNTIF When Cell Contains Specific Text

While working in Microsoft Excel, you may need to find or count specific cell values. You might need to count specific text or value to make a product report or counting presence or checking stock of warehouse. In this article, I will show you how to apply COUNTIF when a cell contains specific text in Excel.

How to Apply COUNTIF When Cell Contains Specific Text

In this article, you will see three easy methods to apply COUNTIF when a cell contains specific text in Excel. In the first method, I will use the COUNTIF function to count if a cell contains specific text. Also, the counts will be exactly matched. In the second method, I will count partially matched string or text values. Finally, I will show you how to count case-sensitive strings.

To illustrate my article further, I will use the following sample data set.

1. Count Exactly Matched String

In my first method, I will count the cells that contain a string that exactly matches the given string. To perform this count, I will need the help of the COUNTIF function. The steps for this procedure are as follows.

Steps:

• Firstly, make four extra fields below the primary data set like the following image.
• Here, I want to count how many times the string HPP-08 is in the data range C5:C12 and I want an exact match for this count.

• Secondly, to perform the count, insert the following formula in cell C15.

• Here, I want to match the exact cell value of B15 and count its presence in the C5:C12 data range.

• Thirdly, press Enter and you will find the desired result.

2. Enumerate Partially Matched String

Suppose, I don’t want to find or count exact matches in the procedure. Rather, I want to perform this task on a portion of the whole string. The procedure for this task is quite similar to the first method. But for the partial match, I will insert a wildcard character in the formula. The character is the asterisk (*) sign. Let’s see the following steps for a better understanding.

Steps:

• First of all, to find how many cells of the data range C5:C12 contain the substring or partial text HPP, type the following formula in cell C15.

• Finally, press Enter and the number of counts will appear as the result.

3. Count Case Sensitive String

The major issue with using the COUNTIF function is that it is case-insensitive. This means, if you have the same text or strings in different cases, the function will count all of them despite wanting only one. To solve this issue, you can use a combination formula of the SUMPRODUCT, ISNUMBER, and FIND functions. You will find the detailed procedure in the following steps.

Steps:

• In the beginning, look at the following image, where I want to count only for the string HPP but the COUNTIF function formula is showing results for both HPP and Hpp.

• In order to solve the issue, use the following formula in cell C15.

• Thirdly, after pressing Enter it will show the result as 1 which is correct for this context.

Things to Remember

• The formula containing a wildcard character or asterisk sign will not work if the data range contains only numeric values. It allows the COUNTIF function to count only text strings.
• If you have case-sensitive values, then use the third method to count cells with specific text.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to apply COUNTIF when a cell contains specific text in Excel. Please share any further queries or recommendations with us in the comments section below.

Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.

<< Go Back to With Text | Count Cells | Formula List | Learn Excel