Excel COUNTIF Function – Count cells that are not blank, greater/less than, or contain a specific value

Excel is equipped with a number of functions that are designed to count various types of cells, including those that are blank, those that are not blank, those that contain numbers, dates, or text values, those that include certain words or characters, and so on. Excel's COUNTIF function is used to determine the number of cells inside a given range that fulfil the requirements of a certain condition. In the criteria, logical operators such as ">, <, =" and wildcards such as "*" and "?" can be used to perform partial matching. It is also possible for criteria to be dependent on the value of another cell.

In this tutorial, let’s explore the use of COUNTIF function based on different criteria.

COUNTIF Function for Non-Blank Cells

COUNTIF can count both blank cells and cells that are not blank.

Step 1

In our example, we have some names and some blank cells in our excel sheet. Let’s calculate how many non-blank cells are there in our example.

See the following image of the Excel sheet which contains names and blank cells.

Step 2

Select one blank cell to store the calculated cell count and type the following formula in the formula bar and press enter.

=COUNTIF(range,"<>")

In our case we have selected cell B6 and wrote the following formula.

=COUNTIF(A2:A14,"<>") 

It will select the range from A2 to A14 and will calculate the non-blank cells. Here in above formula, “<>” which means "not equal to," followed by "nothing." The COUNTIF function counts how many cells are not equal to "nothing." In other words, cells that don't have anything in them.

There is also a function called COUNTA, which tells you how many cells in a given range have any value. In above example we can also write the formula

=COUNTA(A2:A14) 

See the following image

COUNTIF Function for Cells Greater Than a Specific Value

You can count the number of cells whose values are greater than to the number you choose by adding the appropriate operator to the criteria

Step 1

In our example, we have names of the employees with their salary. We want to count the employees whose salary is greater than 20,000. See the below given image.

Step 2

Select one blank cell and add the following formula to formula bar and press enter.

=COUNTIF(range,">value")

In our example, we have selected the cell D2 and adding the following formula to it.

=COUNTIF(C2:C10,">20000") 

After pressing Enter, we can see from the following image given the count of persons whose salary is greater than 20,000.

COUNTIF function for cells less than a specific value

Similarly, you can count the number of cells whose values are less than to the number you choose.

In the above example, let’s suppose we want to calculate the number of employees whose salary is less than 30,000

We have selected the cell D2 and added the following formula to it. After pressing enter we can get the count of employees whose salary less than 30,000.

=COUNTIF(C2:C10,"<30000") 

See the following image.

COUNTIF Function for Cells Equals to a Specific Value

In the above example, let’s suppose we want to calculate the number of employees whose salary is equals to 25,000

We have selected the cell D2 and added the following formula to it. After pressing enter we can get the count of employees whose salary equals to 25,000.

=COUNTIF(C2:C10,"=25000")

See the following image.

COUNTIF Function for Cells Containing a Specific Value

If we want to use COUNTIF to count how many cells have a certain value, we need to know about the "wildcard" characters. Excel has three wildcard characters that can be used with the COUNTIF function: the asterisk (*), the question mark (?) and the tilde (~).

• An asterisk (*) matches any number of characters.

• The question mark (?) works with any character.

• Tilde (~) is the same as the wildcard character.

You can also use COUNTIF function to count cells that contain specific value.

In the above example, let’s suppose we want to calculate the number of "male" employees. We will use the following formula to count.

COUNTIF(B2:B10,"Male") 

After pressing Enter, we can get the number of Male employees. See the below given image.

Conclusion

In this tutorial, we demonstrated how you can use the Excel COUNTIF function to count cells that are not blank, greater/less than, or contain a specific value.