Usage of COUNTIF function in Excel

COUNTIF function in Microsoft Excel is useful to find the count of number of cells which meets the given criteria.

Below is the syntax of this function:

COUNTIF(range, criteria)

  • Here range is any valid cell range.
  • criteria is either number, text, expression, any valid cell reference or any valid formula. It allows to use wildcards in expression.

Following examples shows the usage of COUNTIF function. All these examples are based on the below data.

Data

Data

1. Number criteria

=COUNTIF(A2:A10, 1)

which returns 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

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 which 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 start with the letter “A”. In this case the result is 2. Keep that in mind the search is case in-sensitive; means “A” and “a” treated as same.

  • =COUNTIF(A2:A10, “A??”)

which returns the number of cell values start with the letter “A” and contains exactly 3 characters. In this case the result is 1. Because “Ant” is the only value matches with the criteria “A??”.

4. Cell Reference criteria

We can use valid cell references in criteria. Cell references must NOT be included in “” (double quotes). If we include, Excel will treat them as text.

  • =COUNTIF(A2:A10, A2)

This will return how many times the value in the cell A2 is 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.

by Code Steps