COUNTIF Function
The COUNTIF function is a combination of the COUNT function and a conditional component. It allows you to count the number of cells in a range that meet a certain condition. For example, you can count only the values in a list of orders that exceed $1,000.
Syntax
COUNTIF(range, criteria)
Arguments
Argument
Description
range
Required. This is the group of cells that you want to count. This argument can contain numbers, a named range, or references that contain numbers.
criteria
Required. This is the condition that is used to determine which cells will be counted . This can be a cell reference, text, expression, or function. For example, you can use a number like 40, a logical comparison like ">=40", a cell reference like D10, or a word like "bolts".
Example
In this example, we’re using COUNTIF to count all Sales over $5,000.
The formula we use is:
=COUNTIF(B2:B11,">5000")
The first argument is the range we want to count - B2:B11 .
The second argument is the criteria - greater than $5,000 (“>5000”).
Note that the criterion is included in quotes because it includes a logical symbol.
Other examples
In the following examples, we have a table of data which we query with different COUNTIF formulas. The formulas, results and descriptions are shown below.
Formula 1
Formula
=COUNTIF(A2:A14,"Tea")
Result
3
Description
Counts the number of cells with tea.
Formula 2
Formula
=COUNTIF(A2:A14,A4)
Result
2
Description
Counts the number of cells with peaches (the value in A4).
Formula 3
Formula
=COUNTIF(A2:A14,A2)+COUNTIF(A2:A14,A3)
Result
4
Description
Counts the number of teas and pears in A2:A14.
Formula 4
Formula
=COUNTIF(B2:B14,">20")
Result
2
Description
Counts the number of values in cells B2:B14 greater than 20.
Formula 5
Formula
=COUNTIF(B2:B14,"<>"&B7)
Result
10
Description
Counts the number of cells with a value not equal to 10 in cells B2:B14. The ampersand (&) is used for concatenation.
Formula 6
Formula
=COUNTIF(A2:A14,"T*")
Result
4
Description
Counts the number of items starting with T in cells A2:A14.