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 $:
