Use COUNTIF function in Excel

In this tutorial, we will learn how to use COUNTIF function. COUNTIF function is used to count the number of cells that meet a given criteria or condition. MS Excel 2021 is used.

Syntax

=COUNTIF( cell range, criteria)

• Cell range is the positional value of cells such as A4:D8, where you want to count the cells.
• Criteria is condition which needs to be fulfilled to count the cell. If a cell meets the criteria it is counted, if it does not meet the criteria, it is not counted. The criteria can be an arithmetic condition, text or even a value of cell.

Counting cells with a criteria

We will use an example to understand the function

We have the following data of students, and using COUNTIF function we will count the number of cells for which students have passed.

If a student gets more than 50 marks, he will pass, and the cell will be counted.

Select the cell, where you want the output. Enter formula =COUNTIF(

“=” sign is entered before entering any formula. Parenthesis is entered to enter the parameters of the function

Select the cell range or enter it manually, the formula becomes: =COUNTIF(B2:B15)

We will now enter the criteria to count the cell, that is if the marks of the student is more than or equal to 50. So, in quotation marks enter, >=50.

So, the formula becomes: =COUNTIF(B2:B15,">=50")

The arithmetic conditions are entered in quotation marks.

We see that only those cells have been counted, that have the value greater than or equal to 50.

Similarly, the cells that have values less than 50 can also be counted, and we can count the number of students that have failed.

The data in above image shows that the number of students who have passed or failed based on the counting of cells.

More on counting cells:

• How to count cells in Excel.
• Count the cells with number using COUNT function.
• Count every cell except blank cells using COUNTA function.