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

This is the sample dataset.

### Method 1 – Counting the Exactly Matched String

Use the COUNTIF function.

Steps:

• Create four extra fields.

To count how many times the string HPP-08 is in C5:C12 and have 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:

• Press Enter to see the result.

### Method 2 – Counting a Partially Matched String

To count a partially matched string, how many cells in C5:C12 contain the substring HPP:

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

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

### Method 3 – Counting Case Sensitive Strings

Combine the SUMPRODUCT, ISNUMBER, and FIND functions.

Steps:

To count the string HPP only, the COUNTIF returnsÂ both HPP and Hpp.

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

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

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

Advanced Excel Exercises with Solutions PDF