COUNTIF counts the values in a range of cells that match a given criteria. In this guide, we’re going to show you how to use the COUNTIF function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Syntax
Arguments
| range | The range of cells that you want to apply the criteria against |
| criteria | The criteria to be applied to the range values, and determine which cells are to be counted. |
Examples
We're using named ranges in our examples to make the formulas easier to read. You don't need to do this to run your formulas.
Example 1
Example 2
Note that the COUNTIF function is not case-sensitive - "FIRE" and "fire" criteria will give the same results.
Tips
Comparison operators:
| Operator | Description | Criteria Sample | Criteria Meaning |
| = | Equal to | “=10000” | Equal to 10000 |
| <> | Not equal to | “<>10000” | Not equal to 10000 |
| > | Greater than | “>10000” | Greater than 10000 |
| < | Less than | “>10000” | Less than 10000 |
| >= | Greater than or equal to | “>=10000” | Greater than or equal to 10000 |
| <= | Less than or equal to | “<=10000” | Less than or equal to 10000 |
| ? | Takes the place of a single character | “Admin?” | 6-character word starts by “Admin” |
| * | Can take the place of any number of characters. | “Admin*” | Any number of character word starts with “Admin” |
| ~ | Use tilde in front of a question mark or an asterisk to actually find them | “Admin~*” | Equal to "Admin*" |
Note: Wildcards cannot be used for numeric values. Searching for a wild card character in a range of numeric values will return no matches.
Issues
#VALUE!
The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters, or the string #VALUE!.
Empty Cell Reference
If criteria argument refers to an empty cell, the COUNTIF function evaluates the cell value as 0.
