How to determine if range contains specific text in Excel
In excel, the COUNTIF function can help us know if a range contains specific text and the number of times it appears. This function is so essential especially if you are dealing with large volume of data in excel, as you cannot manually count specific occurrences individually.
It is the vital nature of the COUNTIF function in excel that makes it a MUST know the formula for everyone. But even if you are aware of the existence of this formula, it will be of little help if you do not know how to use it in determining whether or not a range contains specific text. This article will provide a step-by-step guide on how to utilize the COUNTIF excel function.
How does the COUNTIF function work?
For you to understand how the function works, you need to have a dataset to practice with. For illustrative purposes, I have come up with a list of customers and whether they have paid or not. For those who have paid, I have indicated with the text “YES” and for those that have not paid, I have indicated with a “NO”.
The diagram below shows the list of customers with their respective entries:
Figure 1: Spreadsheet where we shall find if a range contains specific text
To count the number of cells that contain a particular text, we shall need the COUNTIF function.
How to use the COUNTIF function to find out if a range contains specific text
Using the function is simple and straightforward. The function looks like the one below:
Range indicates the range of data from which we want to count. Criteria, on the other hand, shows our argument. This can be a value or string. The criteria can as well indicate a logical expression.
How COUNTIF works
Let us proceed with our list of customers and see how the function works. Look at the diagram below for the “YES” entries.
Figure 2: Finding if the Range contains the specific text “YES”
Here, to get the number of “YES” entries, we have to write the function and then specify the criteria.
The range in our case will be C2:C11. While the criteria will be “YES”
So the function will be written as
This will count all the “YES” entries in the range C2:C11
We repeat the same procedure for the “NO” entries, as we have done with the “YES” entries, but use “NO” instead. See the diagram below:
Figure 3: Finding is the range contains the specific text “NO”
This will count the total number that the entry “NO” appears in the specified range.