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)
COUNTIF function with numbers:
Below is the list of 20 students with their marks in the percentage. The teacher is assigned the job to count the students who scored > 33% marks and thus calculate the passing 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 function when the text is given
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 a 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:
Find the Blank and non-blank cells using the COUNTIF formula
This function can be used to find the blank and the non-blank cells in the given range. Suppose the station master wants to know the status of the number of trains with no remarks about their whereabouts. We can help him using the COUNTIF function which is illustrated in the following example:
And the blank cells are as follows:
By now you must have seen and understood the versatility and the usefulness of the COUNTIF function. It is one of the simplest functions which meets your multiple requirements.