COUNTIF (count if condition is true)

<< Click to Display Table of Contents >>

COUNTIF (count if condition is true)

Syntax:

COUNTIF(Range, Criterion)

Description:

Counts those cells in a cell range that fulfill the specified criterion.

Range is the cell range to be evaluated.

Criterion is the condition that the values in Range have to fulfill to be included:

Use numbers or text (like "42" or "bolts") to count all cells that contain that value.

Tip: To count all non-empty cells, use "*" as criterion. For example COUNTIF(D2:D4, "*") counts all cells in the specified area that are not empty.

Use conditions (like ">10" or "<=5") to count all cells that match the specified condition.

Note: Criteria always have to be surrounded by double quotation marks (").

Example:

If the cells A1:A5 contain the values 1, 2, 3, 2, 1: The following applies:

COUNTIF(A1:A5, "2") returns 2

COUNTIF(A1:A5, ">=2") returns 3

Tip: A modification of the formula is required if a cell reference instead of a constant is entered for the search criterion. Here's an example:

Cells A1 to A5 contain the above example values. In addition, cell C1 contains the value 2.
You now want to find out how many values in the range A1:A5 are greater than the value in cell C1.

Then the formula is COUNTIF(A1:A5, ">"&C1) and returns 1

See also:

COUNT, AVERAGEIF, AVERAGEIFS, SUMIF, SUMIFS, COUNTIFS