The Excel COUNTIF function counts the number of numeric values in a given cell range if they meet the specified condition.
Syntax
=COUNTIF(Range, Condition)
| Argument | Argument description | |
|---|---|---|
| 1 | Range | The cell range of which the numbers are counted if they meet the condition. |
| 2 | Condition | The condition that determines which values are counted. Should almost always be enclosed in double |
Example 1: Simple Excel Countif formula
A Countif formula in its simplest form may look like this:
=COUNTIF(A1:A3, ">1")
Explanation
Excel returns 2 because there are two numeric values larger than 1 in the cell range A1:A3.
Example 2: Wildcards and Countif
You can use any condition for the Condition argument of the Countif function. The condition can simply be a text, a number or a date. In such cases, Excel will check if the values are equal to the given condition value.
For example:
=(A1:A3, "Pass")Will count all cells containing the text Pass in the range A1:A3.
But there are more ways you can create conditions. By combining values with conditional operators or with functions that return TRUE or FALSE. If you’d like to know more about these types of conditions, check out this section of our complete guide on the If function.
Finally, another thing that’s allowed is the use of wildcards. Those can be inserted in the Conditionargument.
If you don’t know what wildcards are, they are characters like ? and * that allow you to match with an approximate match instead of an exact match.
Note: for more information on wildcards, check this section about wildcards from our Find and Replace post.
Here’s an example formula in which we count how many of the email addresses are Gmail addresses. We use the asterisk (*) wildcard to match with gmail.com without regarding the first part of the address.
Explanation
The result is 3 because that is the number of email addresses in the cell range A2:A6 ending in gmail.com.
The condition *gmail.com matches with any value ending in gmail.com, regardless of what text is before that. Therefore, we match with every Gmail address and we can count how many there are.
Questions
Yes, you can. The easiest way to do this is by using the Countifs function, which allows for multiple (range, condition) pairs.
An alternative with one range and multiple conditions is to use the regular Sumif function, combined with logical operators (And, Or, Not, Xor).
No, the Countif and Countifs functions are not compatible with arrays as input argument. In fact, none of the functions in the Ifs family are. Instead, you can use the Sumproduct function to achieve the desired result.
No, Countif is not case sensitive. So if your condition is “MAYA” you will also find Maya, mAYA and maya.
Drawbacks
The Excel Countif function has some drawbacks:
- We’ve mentioned it before: you cannot use arrays instead of cell ranges with the COUNTIF or COUNTIFS functions.
- Using Countif to match strings with more than 255 characters will produce incorrect results.