COUNTIFS Function

COUNTIFS is an extension of the COUNTIF function that enables you to count values in multiple ranges using multiple criteria to determine what values to count.

Syntax

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

Arguments

Argument	Description
criteria_range1	Required. The first range you want to evaluate using the associated criteria, which is criteria1.
criteria1	Required. This is the first criteria and it pairs with criteria_range1. It could be a number, cell reference, expression, or text that define which cells will be counted. For example, criteria can be expressed as 40, ">=40", D10, "bolts", or "40".
criteria_range2, criteria2, ...	Optional. Additional ranges and criteria pairs. You can have a total of 127 range/criteria pairs.

A couple of points to take into consideration when using COUNTIFS:

• Each additional range must have the same number of rows and columns as
• criteria_range1 . The ranges do not have to be adjacent to each other.
• If the criteria argument points to an empty cell, the COUNTIFS function treats the empty cell as a zero (0) value.

Example

In the following example, we want to count the number of people for each state with 40 or more orders. This problem requires us to use two criteria to evaluate two columns. We will be using the state name and “>=40” to determine which records meet our criteria.

We apply the following formula to solve the problem:

=COUNTIFS($B$2:$B$12,F2,$C$2:$C$12,G2)

Formula explanation:

=COUNTIFS($B$2:$B$12,F2,$C$2:$C$12,G2)

• The Criteria_range1 argument references the State column B2:B12 (an absolute reference has been used - $B$2:$B$12).
• The Criteria1 argument is F2 . This is a reference to the State we want to use as our criteria. A cell reference has been used for this argument to make it easier to change. This argument has left as a relative reference because we want it to change relative to the cell the formula is being copied to.
• The Criteria_range2 is the No. Orders column ($C$2:$C$12 ). We will be using our second criteria to evaluate this column. Again, use the F4 key to make it an absolute reference.
• The Criteria2 argument is G2 (>=40). A cell reference has been used for this argument to make it easier to change.

We enter the formula in cell H2 and then copy it down the column to count the number of people with orders that match the criteria for each state.