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.


Download Practice Workbook

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


3 Easy Methods 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.

Read More: Excel VBA to Count Cells Containing Specific Text


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.

Read More: Count If Cell Contains Text in Excel (5 Easy Approaches)


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.

Read More: How to Count Cells in Excel with Different Text (5 Ways)


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.

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.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.


Further Readings

Rifat Hassan

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo