COUNTIF Function

Basic Description

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

Syntax: 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.

 

 

Excel Countif Function Examples

Below spreadsheets shows the database for using countif function.

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

 

Countif Function Example 1

The following example shows the Excel Countif function used to count cells containing text strings, numerical values, dates or logical values in the data spreadsheet on the right: The format of the functions is shown in the lower left spreadsheet and the results are shown in the lower right spreadsheet.

 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

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:
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     .
.
.
.
.
.

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

 

Countif Function Example 3

Formulas:

Count_Formulas_1

Results:

Count_Result_1

 

Countif Function Example 4

Formulas:

Countif_Formulas

Results:

Countif_Results

 

Download Documents

Download Excel File

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment