COUNTIFS

Excel Functions

Count cells that match multiple criteria

Syntax

EXCEL

=COUNTIFS(range1, criteria1, [range2], [criteria2], ...)

Arguments

Parameter	Description
`range1`	The first range to evaluate.
`criteria1`	The criteria to use on range1.
`range2`	[optional] The second range to evaluate.
`criteria2`	[optional] The criteria to use on range2.

Return Value

The number of times criteria are met

Details

The COUNTIFS function counts cells in a range when they meet one or more specific conditions. COUNTIFS is one of Excel's most widely used functions, and you find it in all kinds of spreadsheets that perform conditional counts based on dates, text, or numbers. Although common, COUNTIFS has a unique design that splits logical conditions into two parts. As a result, the syntax to define conditions in COUNTIFS can be a little tricky and takes some getting used to. Remember that COUNTIFS uses "AND logic" — to be included in the final count, all conditions must be TRUE. The COUNTIFS function counts the number of cells in a range that meet one or more conditions. The syntax depends on the number of conditions being evaluated. Each condition is provided as a pair of range/criteria arguments: If th

Examples

Syntax

The COUNTIFS function counts the number of cells in a range that meet one or more conditions. The syntax depends on the number of conditions being eva

EXCEL

=COUNTIFS(range,criteria) // 1 condition
=COUNTIFS(range,criteria,range,criteria) // 2 conditions

Basic Example

The four formulas in I5:I8 look like this:

EXCEL

=COUNTIFS(C5:C16,"red")
=COUNTIFS(C5:C16,"red",D5:D16,"TX")
=COUNTIFS(C5:C16,"red",F5:F16,">20")
=COUNTIFS(C5:C16,"red",F5:F16,">20",D5:D16,"TX")

Criteria in another cell

It is often convenient to put criteria in another cell and then refer to this cell inside your formula. This makes it easy to change criteria later wi

EXCEL

=COUNTIFS(range,A1)

Criteria in another cell

When a condition requires an operator, you must concatenate the cell reference to the operator. For example, to count cells in a range greater than A1

EXCEL

=COUNTIFS(range,">"&A1)

Criteria in another cell

Note we are joining the ">" operator to cell A1 with an ampersand (&) character. In the worksheet below, COUNTIFS has been configured to return the co

EXCEL

=COUNTIFS(D5:D9,">"&G4) // count if greater than G4

Not equal to

EXCEL

=COUNTIFS(B5:B9,"<>red") // not red
=COUNTIFS(B5:B9,"<>blue") // not blue
=COUNTIFS(B5:B9,"<>"&E7) // not E7

COUNTIFS

Syntax

Arguments

Return Value

Details

Examples

See Also