The COUNT IF function in Excel is used to count those cells within the given range with the specific condition. In this article, we will see the usefulness and the versatility of the COUNTIF function.
The syntax of the function is as follows:
COUNTIF (range, criterion)
Let us explore this function with some examples having different criterion:
COUNTIF with numbers
Below is the list of 20 students with their marks in the percentage.
She can very easily know the number of students passed in the examination using the COUNTIF function and can calculate the passing percentage of the students.
The passing percentage of the students is 14/20 * 100 = 70%
Let me remind you the working:
COUNTIF with text
Another simple example to match the text and count the number of times it occurred in the selected range of the texts.
Below is the list of the football winners across the world in the last 60 years.
Let us say that I want to know the number of times team Brazil has won the world cup title in the last 60 years.
I can do this very easily using the COUNTIF function which is illustrated in the screenshot shown below:
(*The criteria are case insensitive that is whether you write “brazil” or “Brazil”, the formula will produce the same result)
The working of the formula is as follows:
COUNTIF with partial match
This function can be used to count the partial match of the characters out of the given range.
Suppose you want to know that how many students are there in the class with surname Gupta, irrespective of their different initial name, from the list of the students given below:
The asterisk (*) sign in the formula is used to find and count those cells with any sequence with the text “Gupta”.
Again, if you want to know the names that start with alphabet "A" and it does not matter how many other letters it contains, then use the following formula:
End text matching:
Similarly, if I say that I want to know the students’ name end with “ma” irrespective of the number of the letters at the beginning of “ma”, we can use the following formula: