Excel COUNTIFS Function


What does it do?

The Excel COUNTIFS function counts together the number of cells that meet a given criteria.  Unlike COUNTIF which allows only one range and one criterion, COUNTIFS is able to check up to 127 ranges and 127 criteria.  The criterion can be a text string, number or logical expression, i.e. GREATER THAN, LESS THAN, NOT EQUAL TO, EQUAL TO, GREATER THAN or EQUAL TO, LESS THAN or EQUAL TO and EQUAL TO.  Expressions use the logical operators <, >, <>, = for numerical criterion.  For text, use the wildcards, asterisk (*) or question mark (?) for partial matching.


Syntax Explained

Excel COUNTIFS function in the formula bar showing syntax.

Criteria_range1: This is the range where you want to see if your matching Criteria appears in.

Criteria1: This is the condition to test for in the range, when it is met/found, it will be counted into the result.

Criteria_range2 to 127 (optional): As per Criteria_range1

Criteria2 to 127 (optional): As per Criteria1


Examples

1. COUNTIFS to match a text string.

Example of an Excel COUNTIFS function matching two criteria in two ranges.

In the example above the ranges will ONLY be counted if BOTH conditions are met i.e. a row MUST have RED in the Colours columns and ALSO have a value of 3 or more in the Quantity column.  All other matches are excluded such as row 7 with a Red in the colour column since the Quantity is 2.

Since COUNTIFS uses identical logic to COUNTIF, you can apply the same techniques for partial matches and the logical operators.  You can combine an exact match of text in a range, then a logical operator in the second and a partial match in the third for example.