COUNTIFS function

COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  • criteria_range1 is the range to be checked by criteria1. Criteria_range1 and criteria1 are search pairs and when items that meet ceriteria1 in the range are found, they are counted.
  • criteria1 is the criterion that defines which cells in criteria_range1 will be counted. The criterion can be a number, expression, cell reference, or text.
  • criteria_range2, criteria2, (optional) are additional ranges and their corresponding criteria. You can enter up to 127 range and criteria pairs.
Note:
  • The criterion is applied to one cell at a time. All of the cells that meet their corresponding criteria are counted.
  • If the criteria argument is a reference to an empty cell, the COUNTIFS function treats it as a 0 value.
  • You can use wildcard characters in criteria, for example, the question mark (?) and asterisk (*). If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example

=COUNTIFS(A2:A9, "<7", A2:A9,">2")

Counts how many numbers between 2 and 7 (not including 2 and 7) are included in cells A2 through A9.