Using Excel's COUNTIFS function

Many users of Excel's COUNTIF function wonder whether it's possible to do a COUNTIF based on multiple criteria rather than a single condition. It turns out that there's an easy way to do this: Excel's COUNTIFS function. Like SUMIFS, COUNTIFS will allow you to take the count of rows or columns in a table that satisfy multiple specified conditions.

For example, let's say that an artist has a table that shows the number of paintings she has sold by customer, month, canvas type, and paint type. Using COUNTIFS, she can calculate the number of paintings sold to a particular customer in January; the number of oil paintings sold in March; or the number of acrylics a particular customer has purchased.

Defining the problem

Take a look at the following spreadsheet, which lists SnackWorld's sales by item, customer, and month.

Let's assume that a SnackWorld executive wants to find the number of customers who bought Brownies in January. It wouldn't be possible to do this calculation with COUNTIF, because COUNTIF only allows you to specify one conditional criteria. So, we'll use a COUNTIFS to look things up across multiple criteria.

Using COUNTIFS

Here's the syntax for the COUNTIFS function:

=COUNTIFS(criteria_range_1, criteria_1, criteria_range_2, criteria_2...)

The function is very similar to the basic COUNTIFS, except that it allows for multiple sets of criteria_range and criteria pairs. Note that the COUNTIFS function accepts an unlimited number of arguments, as long as they come in pairs (because every criteria_range needs a criteria to check against).

Let's apply the above formula to our SnackWorld example, using it to calculate the number of customers who bought Brownies in January:

=COUNTIFS(B3:B10, =&"Brownies", E3:E10, =&"January")
Output: 2

First, the above formula looks at our initial criteria_range, B3:B10, and compares it against our initial criteria, "="&"Brownies", to determine how many rows satisfy our query. Then, as a next step, it looks at our secondary criteria_range, D3:D10, and references it against our secondary criteria, "="&"January". The resulting output is the number 2, because two rows in our table list the phrase Brownies in the Item column and the phrase January in the Month column.

Note that like COUNTIF, COUNTIFS doesn't require the "="& construction when the criteria we are using contains the operator =. So, the above formula can be simplified as follows:

=COUNTIFS(B3:B10, "Brownies", E3:E10, "January")
Output: 2

COUNTIFS with a cell reference

Like SUMIFS, COUNTIFS becomes particularly useful when we combine it with dynamic cell references. Take a look at the following example, in which we've built dynamic input cells to help count the number of sales in a given month that are over a particular dollar threshold:

=COUNTIFS(D3:D10, H4, E3:E10, ">="&H5)
Output: 2

In the above example, we have replaced our criteria arguments with cell references to dynamic input cells that we've created on the sheet. If we wanted to, we could add even more input cells to restrict our COUNTIFSto an even narrower set of criteria — for example, we could add a switch that only includes rows of sales to a particular customer.

Using COUNTIFS instead of COUNTIF

As with SUMIFS and SUMIF, COUNTIFS can be used to replace COUNTIF entirely. If you just want to count based on one set of criteria, simply include only one set of criteria within the COUNTIFSfunction. We find that it's easier to just use COUNTIFS all the time to simplify formula memorization — but feel free to use the functions however is easiest for you!

Comments