How to use the COUNTIF function in Excel?

​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)

• The range is the number of cells you specify in your worksheet and this can be specified as A1:A10 or B1:B20.
• The criterion is the condition which enables the Excel to count those cells, out of the range of cells, which meet your requirement. For example, if I set the criterion to count those cells which are greater than 25, the Excel will select these specific cells, count them and will produce the result.

Let us explore this function with some examples having different 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 (C2:C21,”>33%”)

• The Range is from C2 to C21 and
  
• The criterion is that it should be > 33%
• Result: 14 (There are 14 such students who scored more than 33% marks in the examination)

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 (B2:B17,”Brazil”)

• Range is B2:B17
• Criterion is “Brazil”
• Result: 5 (Excel will count the number of time Brazil occurs in the given range and produce the result)

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:

Conclusion

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