COUNTIF

Excel Functions

Count cells that match criteria

Syntax

EXCEL

=COUNTIF(range, criteria)

Arguments

Parameter	Description
`range`	The range of cells to count.
`criteria`	The criteria that controls which cells should be counted.

Return Value

A number representing cells counted.

Details

The COUNTIF function counts cells in a range when they meet a specific condition. COUNTIF is one of Excel's most widely used functions, and you will find it in all kinds of spreadsheets that calculate conditional counts based on dates, text, or numbers. While powerful, COUNTIF has a unique syntax that splits logical conditions into two parts, making it different from many other Excel functions. This syntax takes a little getting used to. Remember that COUNTIF can only apply one condition. For multiple conditions, use the COUNTIFS function. The generic syntax for COUNTIF looks like this: The criteria is applied to cells in the range. When cells meet the criteria, they are added to the count. In the worksheet below, we have a small amount of sales data. We use COUNTIF in three formulas to pe

Examples

Basic Example

The generic syntax for COUNTIF looks like this:

EXCEL

=COUNTIF(range,criteria)

Basic Example

The criteria is applied to cells in the range. When cells meet the criteria, they are added to the count. In the worksheet below, we have a small amou

EXCEL

=COUNTIF(D5:D16,">100") // count sales over 100
=COUNTIF(B5:B16,"jim") // count sales by Jim
=COUNTIF(C5:C16,"CA") // count sales in CA

Criteria in another cell

A great way to use COUNTIF is to put criteria in another cell and then refer to this cell inside your formula. This makes it easy to change criteria l

EXCEL

=COUNTIF(range,A1)

Criteria in another cell

If you need to include an operator in the criteria, you must concatenate the cell reference to the operator. For example, to count cells that are grea

EXCEL

=COUNTIF(range,">"&A1)

Criteria in another cell

Note we are joining the ">" operator to cell A1 with an ampersand (&) character. In the worksheet below, COUNTIF has been configured to return the cou

EXCEL

=COUNTIF(D5:D16,">"&F5) // count if greater than G4

Not equal to

EXCEL

=COUNTIF(B5:B9,"<>red") // not equal to "red"
=COUNTIF(B5:B9,"<>blue") // not equal to "blue"
=COUNTIF(B5:B9,"<>"&E7) // not equal to E7

COUNTIF

Syntax

Arguments

Return Value

Details

Examples

See Also