How to use the COUNTIFS function

Let’s take a look.

In this first set of tables, we have twonamed ranges, “number” and “color.”

The first item in the pair is the range, and the second item is the criteria.

Note that all ranges that you use must always be the same size.

For the second example, we can see two pairs in action.

The “number” range appears twice, each time with a different condition.

The result is “1”.

In the next set of tables, we’re using a named range called “fruit.”

The first example shows that COUNTIFS like COUNTIF is not case-sensitive.

In the next example, we see that COUNTIFS conditions are joined with AND.

One option is simply to use two COUNTIF functions together.

Just add one to the other.

Another option, in this case, is to use a list to specify two items in one condition.

You’ll need to use curly braces and also wrap the entire COUNTIF function in the SUM function.

Both formulas return “4”.

Then we use the range “department” with “Sales” for the criteria.

The formula returns “1”.