Microsoft Excel is an incredibly useful tool for analyzing datasets containing multiple variables. Often, it’s useful to find the number of cells that meet a series of specified conditions. Counting manually becomes impossible with large datasets. Fortunately, Excel has an easy tool to help you. Let’s learn how to use the COUNTIFS function in Microsoft Excel.
How to Use the COUNTIFS Function in Microsoft Excel
Imagine that you run a website, and you use Excel to list your customers. You have a pair of columns listing their first and last names, respectively. You’ve also gathered key data points. These include:
- Are the customers on your email subscription list?
- Have the customers made a purchase from you in the last 12 months?
It’s easy to filter by each criteria by itself, but for robust analysis you want to explore multiple variables at once. At a glance, for instance, you want to know the answer to two questions:
1) How many customers subscribe to your emails, but haven’t made a purchase in the last 12 months?
2) How many customers subscribe to your emails, and have made a purchase in the last 12 months?
Again, if you have a small dataset, you could count this manually. But for a lengthy list, this becomes both tedious and inaccurate. And as usual, Excel has a built-in function to help you.
That function is called COUNTIFS. COUNTIFS is simple, and yet powerful. All it does is return to you – in a chosen cell – the count of cells that match multiple specified criteria. COUNTIFS is related to Excel’s COUNT function, but unlike COUNT, incorporates more than one criteria or variable.
It’s also worth noting that COUNTIFS doesn’t have to stop at two criteria. You can make the function and formula as long as you want. This enables you to convert extremely large and complex datasets into simple counts of only cells meeting the given conditions.
Let’s begin by using COUNTIFS to answer question one: how many customers subscribe, but haven’t made a recent purchase?
Click into an empty cell, and begin by typing =. The = tells Excel that you’re inputting a formula. Continue with COUNTIFS(. So far, your formula reads:
=COUNTIFS(
Excel then asks for two key inputs for each variable: your criteria range and your criteria inputs. The first criteria here is the cells within the Subscribed? column. Click and drag to select them, in the range C2:C8. Then, add a comma.
=COUNTIFS(C2:C8,
Now, add your criteria input. You’re looking for people who are subscribed to your email list – denoted in the column by the word Yes. For the purposes of the formula, the criteria input should be enclosed in quotations:
=COUNTIFS(C2:C8,”Yes”,
Now, it’s time for the second variable: users that have not made a purchase, indicated in the data by the word No. Select the appropriate range, the Purchase? cells in D2:D8, and type “No”).
=COUNTIFS(C2:C8,”Yes”,D2:D8,”No”)
Hit Enter on your keyboard, and Excel returns the count of email subscribers who haven’t made a purchase: 3.
Now, consider your second question. You want to know how many email subscribers have made a purchase in twelve months. Your formula is virtually identical; the only change comes with the second condition. The “No” in the prior formula should change to “Yes.”
The formula here is:
=COUNTIFS(C2:C8,”Yes”,D2:D8,”Yes”)
Input the formula in a separate cell, then hit Enter again. Excel will run the COUNTIFS formula again, and return your solution: 3 customers that meet the two Yes conditions.
COUNTIFS works with a variety of characters. You can match text conditions as we did above, or analyze data for exact matches, values greater than or less than others, and more. Whatever criteria you specify, COUNTIFS helps return counts of cells matching the conditions you’re searching for. It’s a quick, easy, and powerful way to analyze data in Excel.
Count in Excel