Although common, COUNTIFS has a unique design that splits logical conditions into two parts.
The article below has many working examples.
The syntax depends on the number of conditions being evaluated.
Each condition is provided as a pair ofrange/criteriaarguments:
If there are two conditions, there will be tworange/criteriapairs.
If there are three conditions, there will be threerange/criteriapairs, and so on.
COUNTIFS can handle up to 127 separaterange/criteriapairs.
Using COUNTIFS, we can count orders in the data that meet multiple conditions.
Column H shows the criteria used inside COUNTIFS and column I contains the resulting count.
Each condition requires a separate range/criteria pair, and there are three pairs in total.
Each condition requires a separaterangeandcriteria, and operators need to be enclosed in double quotes ("").
This makes it easy to change criteria later without editing the original formula.
Notice the greater than operator (>), which is text, must be enclosed in quotes.
Note that the second formula will count cells that containany value.
The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell.
The formula above willnotcount a 3-digit number like 123 or 812.
The worksheet below shows how wildcards can be used with the COUNTIFS function.
to match a question mark (?)
that occurs at the end of “Montana?”
and “Michigan?”.
The tilde (~) is an escape character that allows you to find aliteralwildcard.
To match a question mark (?
), use “~?
For more details,see this example.
Summary Table
you’ve got the option to use COUNTIFS to create a simple summary table.
In the worksheet below, we have a list of unique colors in F5:F9.
The goal is to subtotal the amounts in column D by color.
For more advanced users, this is an important limitation in how COUNTIFS works and what it can do.
One of the more tricky limitations of COUNTIFS is that it won’t allow anarrayfor arangeargument.
This formula works fine, but it’s a little complicated.
This would be cool if it worked.
However, Excel won’t even let you enter this formula.
However, COUNTIFS is not programmed to handle arrays, so it won’t work.
How can we work around this problem?
However, the latest version of Excel offers powerful alternatives, includingBYROW,BYCOL, andGROUPBY.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
COUNTA does not count empty cells.
