How to use COUNTIF function in Excel?

COUNTIF is very useful function in Excel. It allows to calculate, how many cells in given cell range include specified criteria.

SYNTAX of COUNTIF function


Range – required; in simple words, this argument tell where do you want to find specified Criteria;
Criteria – required; it defines, what would you like to find in cells specified in Range.

To use COUNTIF function, start typing “= COUNTIF(” in the formula bar, and then use the fx icon to open the window with arguments specification:

How to define COUNIT criteria?

Criteria for COUNTIF function has to be defined in a specific way – the same way as used for SUMIF function. In table below you can find the most common examples for COUNTIF criteria:

CriteriaCorrect criteria definition
Less than 0“<0”
Greater than 0“>0”
Equals to 0“=0”
Less or equals to 0“<=0”
Greater or equals to 0“>=0”
Different than 0“<>0”
Blank cells“”
Not blank cells“<>”
Text in cell begins with abc“abc*”
Text in cell ends with abc“*abc”

Example 1 – basic usage of COUNTIF function

Table below contains list of employees with the information, which department they work at and how much they earn per month:

For this example, we would like to know:

How many employees work in Accounting Department?

How many employees earn more than $1,000 per month?

How many employees work in this company?

In the last example, I have used “<>” as criteria, because I would like to find and count all non-empty cells in column A, which define name of employee.

Here are the results I have received:

Example 2 – criteria defined in different cell

Table below contains information about sales revenue divided by product categories:

In cells A20, A21 and A22 there are 3 categories, and I would like to know, how many times these categories has been listed in table above.

Correct formula to be used is:

This formula can be dragged down for remaining categories, but we need to remember about correct cell references by adding $:

File with examples – download

Leave a Reply

Your email address will not be published.