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
- How to define COUNIT criteria?
- Example 1 – basic usage of COUNTIF function
- Example 2 – criteria defined in another cell
- File with examples – download
SYNTAX of COUNTIF function
=COUNTIF(range;criteria)
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:
Criteria | Correct 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 $:
