Related Functions:

The COUNTIF function returns the number of cells in a supplied range, that satisfy a given criteria.

Syntax

=COUNTIF(range,criteria)

Arguments

Argument Description
range The range of cells that should be tested against the supplied criteria and counted if the criteria is satisfied
criteria A user-defined condition thatis tested against each of the cells in the range. The criteria can be either:

  a numeric value, which may be an integer, decimal, date, time, or logical value, eg. 10, 01/01/2008, TRUE, or
  a text string, eg. “Text”, “Monday”, or
  an expression, eg. “>19”, “<>0”

Criteria an be supplied to the function either directly, as a reference to a cell containing the criteria, or as a value returned from another function or formula.

Note: If your criteria is a text string or an expression, this must be supplied to the function in quotes.

Also note: the COUNTIF function is not case-sensitive. So, the text strings “TEXT” and “text” will be considered to be equal.

Examples

  A B C D E F G
1 Day Date Number T/F Formula Result Notes
2 Sunday 9/7/2013 0 TRUE =COUNTIF(A2:A10,”Wednesday”) 3  
3 Monday 9/8/2013 2.1 TRUE =COUNTIF(A2:A10,”<>Wednesday”) 6  
4 Wednesday 9/10/2013  2 TRUE =COUNTIF(B2:B10,”>01/10/2013″) 2  
5 Thursday 9/11/2013 3 FALSE =COUNTIF(C2:C10,0) 2  
6 Wednesday 9/17/2013 2.5 FALSE =COUNTIF(C2:C10,”>=3″) 4  
7 Tuesday 9/23/2013 3 FALSE =COUNTIF(D2:D10,TRUE) 3  
8 Wednesday 9/24/2013 6 FALSE      
9 Sunday 10/5/2013 4 FALSE      
10 Saturday 10/11/2013 0 FALSE      
11     AA11   =COUNTIF(A$11:A11,A11) 1 Absolute and Relative cell references are used to ensure that, as the function is copied down to subsequent rows, the array always refers to the cells of column A.
12     BB22   =COUNTIF(A$11:A12,A12) 1
13     CC33   =COUNTIF(A$11:A13,A13) 1
14     AA11   =COUNTIF(A$11:A14,A14) 2 <= result indicates repeated values
15     DD44   =COUNTIF(A$11:A15,A15)    

Usage note: You can also use the following wildcards in the search_text argument:

      ?  –  matches any single character
  *  –  matches any sequence of characters

eg. the condition “A*e” will match any substring beginning with “A” and ending in “e”