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