Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Countif-Function

The Excel COUNTIF Function

Related Functions:
COUNT Function
COUNTA Function
COUNTIFS Function

Function Description

The Excel Countif function returns the number of cells within 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 supplied 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.

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

The supplied criteria can be either:

or or

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 evaluated as equal.


Excel Countif Function Examples

Countif Function Example 1

  A B C D
1 Sunday 07-Sep-2008 0 TRUE
2 Monday 08-Sep-2008 2.1 TRUE
3 Wednesday 10-Sep-2008 2 TRUE
4 Thursday 11-Sep-2008 3 FALSE
5 Wednesday 17-Sep-2008 2.5 FALSE
6 Tuesday 23-Sep-2008 3 FALSE
7 Wednesday 24-Sep-2008 6 FALSE
8 Sunday 05-Oct-2008 4 FALSE
9 Saturday 11-Oct-2008 0 FALSE

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

The functions and the results 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
11 3
12 6
13 2
14 2
15 4
16 3

Countif Function Example 2

In the following example, the Excel Countif function is used to identify duplicates in a column containing reference numbers. The function works by counting the number of times the reference number in column A of the current row has occurred so far.

Note that the function in this example uses a combination of relative and absolute cell references, so that, as the formula in cell B2 is copied down column B of the spreadsheet, the reference to A$2:A2 is automatically updated to A$2:A3, A$2:A4, A$2:A5, etc.

This ensures that only the repeated instances of a duplicate value are highlighted. I.e. The function does not highlight the first instance of a value.

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

The above 'Results' spreadsheet shows that the Excel Countif function has, as expected, identified the duplicate reference in cell A5 of the spreadsheet.


For further examples in which the Countif function is used to identify duplicates, see the Find Excel Duplicates and Remove Excel Duplicates pages of this site. The Microsoft Office website also provides further examples of this function.


Countif Function Common Error

If you get an error from the Excel Countif Function, this is likely to be the #VALUE! error:

Common Error
#VALUE! - Occurs if the supplied criteria argument is a text string that is greater than 255 characters in length.
Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions