A to Z of Excel Functions: The COUNTIFS Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the COUNTIFS function.

The COUNTIFS function

This function applies one or more criteria to cells across multiple ranges and counts the number of times all criteria are met.  This is essentially the “multiple” version of COUNTIF (please link to COUNTIF).

The COUNTIFS function employs the following syntax to operate:

The COUNTIFS function has the following arguments:

• criteria_range1: this is required and represents the first range in which to evaluate the associated criteria
• criteria1: this is also required.  The criteria must be in the form of a number, expression, cell reference, or text that define which cells will be counted.  For example, criteria can be expressed as 32, ">32", B4, "apples", or "32"
• criteria_range2, criteria2, ...: these arguments are optional but must appear in associated pairs.  Up to 127 range / criteria pairs are allowed.

It should be further noted that:

• COUNTIFS ignores upper and lower case in text strings.  Criteria are not case sensitive, so “red” and “RED” will match the same cells
• each range's criteria is applied one cell at a time.  If all of the first cells meet their associated criteria, the count increases by 1.  If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated
• if the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a zero value
• wildcard characters are permitted.  The characters, question mark (?) and asterisk (*) can be used in criterion.  The question mark matches any single character, whereas an asterisk matches any sequence of characters.  If you actually want to find a question mark or asterisk use the tilde (~) in front of the required character.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.