The Excel COUNTIF Function

Basic Description

The Excel Countif function returns the number of cells (of a supplied range), that satisfy a given criteria.

The syntax of the function is :

COUNTIF( range, criteria )

Where the function arguments are as follows:

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 that is tested against each of the cells in the range.

Wildcards

You can also use the following wildcards in text-related criteria:

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

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The criteria can be either:

oror

and can 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 that if your criteria is a text string or an expression, this must be supplied to the function in quotes.

Also note that the Excel Countif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be equal.


Excel Countif Function Examples

Countif Function Example 1

 ABCD
1Sunday07-Sep-20080TRUE
2Monday08-Sep-20082.1TRUE
3Wednesday10-Sep-20082TRUE
4Thursday11-Sep-20083FALSE
5Wednesday17-Sep-20082.5FALSE
6Tuesday23-Sep-20083FALSE
7Wednesday24-Sep-20086FALSE
8Sunday05-Oct-20084FALSE
9Saturday11-Oct-20080FALSE

The following example shows the Excel Countif function used to count cells containing text strings, numerical values, dates or logical values in the above data spreadsheet on the right.

The format and results of the functions are shown in the spreadsheets below.

 Formulas:
 A
11=COUNTIF( A1:A9, "Wednesday" )
12=COUNTIF( A1:A9, "<>Wednesday" )
13=COUNTIF( B1:B9, ">01/10/2008" )
14=COUNTIF( C1:C9, 0 )
15=COUNTIF( C1:C9, ">=3" )
16=COUNTIF( D1:D9, TRUE )
 Results:
 A
113
126
132
142
154
163

Countif Function Example 2

The example below shows the Excel Countif function used to identify duplicates in a column containing reference numbers. Note that the function in this example is written so that it highlights only the second, third, etc instance of a duplicate value. - It does not highlight the first instance of the value.

The function works by counting the number of times the reference number in column A of the current row has occurred so far.

Absolute and Relative cell references are used in the definition of the supplied array, (defined as A$2:A2 in the first cell). This ensures that, as the function is copied down to subsequent rows, the array always refers to the cells of column A, up to the current row (ie. A$2:A3 in row 3, A$2:A4 in row 4, etc)

As the function only searches rows up to and including the current row, the result will be 2 (or greater) only for repeated values.

 Formulas:
 AB
1Ref 
2AAA111=COUNTIF( A$2:A2, A2 )
3BBB222=COUNTIF( A$2:A3, A3 )
4CCC333=COUNTIF( A$2:A4, A4 )
5AAA111=COUNTIF( A$2:A5, A5 )
6DDD444=COUNTIF( A$2:A6, A6 )
7    .
    .
    .
 
    .
    .
    .
 
 Results:
 AB
1Ref 
2AAA1111
3BBB2221
4CCC3331
5AAA1112
6DDD4441
7    .
    .
    .
 
.
.
.
 

In the above example the Excel COUNTIF function has, as expected, identified the duplicate reference in cell A5


For a further example of the COUNTIF function being used to identify duplicates, see the Excel Duplicates page of this site. The Microsoft Office website also gives more examples of this function.