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