COUNTIF function

COUNTIF returns the number of cells that meet criteria within a cell range.

Syntax

COUNTIF(range, criteria)

  • range is the range to which the criteria are to be applied.
  • criteria indicates the criteria in the form of a number, an expression, or a character string. These criteria determine which cells are counted. You can also indicate a cell range that contains the search criterion. If you search for literal text, enclose the text in double quotation marks. Criteria can also include wildcard characters. To represent any single character, use ? (question mark) in the text that specifies the condition. To represent any number of consecutive characters, use * (asterisk). For example, use B* to match all entries that begin with B, and B??? to match all entries that begin with B and contain three other characters. To represent an actual question mark or asterisk, use ~ (tilde) to precede the ? or *.

Example

A1:A10 is a cell range containing the numbers 2000 - 2009. Cell B1 contains the number 2006. In cell B2, you enter a formula:

=COUNTIF(A1:A10,2006) returns 1.

=COUNTIF(A1:A10,B1) returns 1.

=COUNTIF(A1:A10,">=2006") this returns 4(2006-2009).

=COUNTIF(A1:A10,"<"&B1) when B1 contains 2006 returns 6.

=COUNTIF(A1:A10,C2) where cell C2 contains the text >2006 counts the number of cells in the range A1:A10 which are >2006.

To count only negative numbers, enter =COUNTIF(A1:A10,"<0")