How to Apply the COUNTIF Function When a Cell Contains Specific Text – 3 Methods

This is the sample dataset.

3 Easy Methods to Apply COUNTIF When Cell Contains Specific Text


Method 1 – Counting the Exactly Matched String

Steps:

  • Create four extra fields.

To count how many times the string HPP-08 is in C5:C12 and get an exact match:

  • Enter the following formula in C15.
=COUNTIF(C5:C12,B15)

To match the exact cell value of B15 and count it in C5:C12:

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

  • Press Enter to see the result.


Method 2 – Counting a Partially Matched String

To count how many cells in C5:C12 contain the substring HPP:

  • Enter the following formula in C15.
=COUNTIF(C5:C12, "*HPP*")

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

  • Press Enter and the number of counts will be displayed.


Method 3 – Counting Case Sensitive Strings

Steps:

To count the string HPP only:

  • Use the following formula in C15.
=SUMPRODUCT(--(ISNUMBER(FIND(B15,C5:C12))))

Formula Breakdown

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

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

  • Press Enter to see the result: 1.

Things to Remember

  • The formula containing a wildcard character or asterisk sign will not work if the data range contains numeric values only.

Download Practice Workbook

Download the free Excel workbook here.


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

Get FREE Advanced Excel Exercises with Solutions!
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