COUNTIFS

Using the Excel COUNTIFS Function

Summary

The Excel COUNTIFS function accepts a set of criteria and returns a count of the cells that meet one or more of the criteria. The function can work with dates, numbers, text, and various other conditions, including wildcards and logical operators.

Syntax

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

Syntax Breakdown

Criteria Range 1
Required. The first range in which to evaluate the criteria.

Criteria 1
Required. Criteria that define which cells will be counted. The argument can be in the form of a number, expression, cell reference, or text. For example, criteria can include 5, "<5", A5, or "5".

Note that non-numeric criteria must be enclosed in double quotation marks (""). This does not apply to numeric criteria.

Criteria Range 2 and Criteria 2
Optional. Additional ranges and criteria. The function can accept up to 127 range/criteria pairs. Additional ranges must have the same number of rows and columns as criteria_range1. However, the ranges need not be adjacent to each other.

Additional ranges that do not match the number of rows and columns of criteria_range1 will result in the function returning the #VALUE! error.


Usage Notes

COUNTIFS counts the number of cells that meet one or more of a specified criteria. The COUNTIFS function allows the user to specify more than one criteria, unlike the COUNTIF function which only accepts one set of criteria.

Criteria and ranges must be supplied in pairs.

When using a wildcard character, the question mark (?) is used to find a single character and the asterisk (*) is used to find a sequence of characters. To find a literal question mark or asterisk, use a tilde (~) in front of the question mark or asterisk.

If you only want to count cells that contain numbers, use the COUNT function.