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