How to Apply COUNTIF When Cell Contains Specific Text

Get FREE Advanced Excel Exercises with Solutions!

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.

3 Easy Methods to Apply COUNTIF When Cell Contains Specific Text


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.
=COUNTIF(C5:C12,B15)
  • Here, I want to match the exact cell value of B15 and count its presence in the C5:C12 data range.

Counting Exactly Matched String as An Easy Method to Apply COUNTIF When Cell Contains Specific Text

  • 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.
=COUNTIF(C5:C12, "*HPP*")

Enumerating Partially Matched String as An Easy Method to Apply COUNTIF When Cell Contains Specific Text

  • 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.
=SUMPRODUCT(--(ISNUMBER(FIND(B15,C5:C12))))

Formula Breakdown

=SUMPRODUCT(–(ISNUMBER(FIND(B15,C5:C12))))

  • Firstly, the FIND function goes through each cell of the data range C5:C12 and searches for the value of cell B15. The function searches for an exact match and returns the matching position.
  • Then, the ISNUMBER function converts the matching numbers into TRUE and everything else into FALSE.
  • Thirdly, the two minus signs convert the TRUE into 1’s and the FALSEs into 0’s.
  • Finally, the SUMPRODUCT function returns the sum of the total array.

  • 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo