Rifat Hassan
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.
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.
- 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*")
- 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.
