The COUNTIF function returns the number of values that meet the specified criterion.
This is a useful tool for analyzing data when you need to find out how many values satisfy specified conditions.
Syntax
COUNTIF(range, criterion)
Argument |
Description |
Permitted values |
|---|---|---|
range |
Range that is checked for compliance with the “criterion” argument |
Any range or array |
criterion |
Condition that applies to the “range” argument |
Number, text, cell reference, or expression: •Equal to: “text”, 1, “=text”, or “=1” •Greater than: “>1” •Greater than or equal to: “>=1” •Less than: “<1” •Less than or equal to: “<=1” •Not equal to: “<>1” or “<>text” •Text with wildcards “?” and “*”. |
Examples of use
Counting cells equal to a specific value
If you have data in the range A1:A10 and want to count how many times the number 10 appears:
=COUNTIF(A1:A10, 10)
The formula calculates the number of cells containing the number 10.
Counting cells that match the text criterion
If cells A1:A10 contain names and you want to know how many of them are equal to “Ivan”:
=COUNTIF(A1:A10, "Ivan")
The formula will return the number of cells with the name “Ivan.”
Counting cells based on the condition that they are greater than a specified value
If you need to count the number of cells with numbers greater than 20:
=COUNTIF(A1:A10, ">20")
The formula counts the number of cells with a number greater than 20.
Counting cells using wildcards
You can use wildcards such as “*” (any set of characters) and “?” (any single character).
For example, to count the number of cells containing text beginning with “A”:
=COUNTIF(A1:A10, "А*")
The result is the number of cells whose text begins with “A”.
Counting cells with conditions based on other cells
If you want to count the number of cells that are equal to the value in cell B1:
=COUNTIF(A1:A10, B1)
The formula will return the number of cells equal to the value in B1.
Notes
–Criteria may include logical operators: >, <, =, <>, as well as wildcards and patterns using the substitution characters “*” and “?” (for example, “*text*”).
–The COUNTIF function does not count empty cells.
–If you need to use multiple criteria, consider using the COUNTIFS function.