The COUNTIFS function returns the number of values that meet all specified criteria.
This can be useful when complex conditions need to be taken into account for data analysis.
Syntax
COUNTIFS(criterion_range1, criterion1, [criterion_range2, criterion2, ...])
Argument |
Description |
Permitted values |
|---|---|---|
criterion_range1 |
Range that is checked for compliance with the “criterion1” argument |
Any range of cells |
criterion1 |
Template or criterion to apply to the “criterion_range1” 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 “*”. |
[criterion_range2, criterion2, ...] |
(optional) Additional ranges or criteria for calculation |
Similar to the previous one |
Examples of use
Counting cells with multiple criteria
If you have sales data in the range A1:A10 (sales amount) and region data in the range B1:B10, and you want to count the number of sales exceeding 1000 in the “North” region:
=COUNTIFS(A1:A10, ">1000", B1:B10, "North")
The expression counts the number of cells where sales are greater than 1000 and the region is “North.”
Counting based on text criteria
If the names of employees are in the range A1:A10 and their departments are in B1:B10, and you want to know how many employees in the Marketing department have the name Anna:
=COUNTIFS(A1:A10, "Anna", B1:B10, "Marketing")
The expression counts the number of employees named Anna in the Marketing department.
Using the comparison criterion
If you have two ranges: B1:B10 (prices) and C1:C10 (categories), and you want to count the number of items in the “Electronics” category with a price above 500:
=COUNTIFS(B1:B10, ">500", C1:C10, "Electronics")
The expression counts the number of items in the “Electronics” category with a price above 500.
Calculation based on conditions derived from other cells
If you have values in cells D1 and E1, and you want to count the number of cells in the range A1:A10 that are equal to the value in D1 and meet the criterion in E1:
=COUNTIFS(A1:A10, D1, B1:B10, E1)
The expression counts the number of cells equal to D1 and corresponding to E1.
Notes
–All ranges must have the same size, otherwise the function will return the #VALUE! error.
–Criteria may include logical operators: >, <, =, <>, as well as wildcards and patterns using the substitution characters “*” and “?” (for example, “*text*”).
–The COUNTIFS function allows you to take into account up to 127 pairs of ranges and criteria.