COUNTIF function in Microsoft Excel is useful to find the count of a number of cells that meet the given criteria.
Below is the syntax of this function:
COUNTIF(range, criteria)
- Here the range is any valid cell range.
- criteria are either number, text, expression, any valid cell reference, or any valid formula. It allows using wildcards in expression.
The following examples show the usage of the COUNTIF function. All these examples are based on the below data.
1. Number criteria
=COUNTIF(A2:A10, 1)
that return how many times the number 1 is repeated. In this case, the result is 2.
2. Text criteria
=COUNTIF(A2:A10, "Apple")
which returns how many times the text “Apple” is repeated. In this case, the result is 1.
=COUNTIF(A2:A10, "1")
which returns how many times the number 1 is repeated. In this case, the result is 2.
3. Expression criteria
The expression must be written in “” (double quotes).
=COUNTIF(A2:A10, "> 100")
which returns the number of values in the given range A2:A10 which are greater than 100. In this case, the result is 2. Because the values 1000 and 2013 are the only values that are greater than 100.
As mentioned above, we can use wildcards “*” and “?” in the expressions. “*” wildcard substitutes any number of characters, whereas “?” wildcard substitutes for exactly one character.
=COUNTIF(A2:A10, "A*")
which returns the number of cell values starting with the letter “A”. In this case, the result is 2. Keep that in mind the search is case insensitive; means “A” and “a” are treated as the same.
=COUNTIF(A2:A10, "A??")
which returns the number of cell values starting with the letter “A” and contains exactly 3 characters. In this case, the result is 1. Because “Ant” is the only value that matches with the criteria “A??”.
4. Cell Reference criteria
We can use valid cell references in the criteria. Cell references must NOT be included in “” (double quotes). If we include them, Excel will treat them as text.
=COUNTIF(A2:A10, A2)
This will return how many times the value in cell A2 has appeared in the range A2:A10. The value in A2 is “1” and it appears in the range A2:A10, 2 times. In this case, the result is 2.