How to Use the COUNTIFS Function in Excel

This article explains how to use COUNTIF to find how many times spreadsheet data meets specific conditions. Instructions apply to Excel 2016 and later.

How to Use The COUNTIFS Function in Excel

The COUNTIFS function can be input manually or using Excel's Formulas menu. In either case, the eventual formula will look something like:

=COUNTIFS(D4:D17,"Yes",E4:E17,">=5")

In this example, the COUNTIFS function searches through cells D4 to D17, looking for the text Yes and through cells E4-E17 for numbers that are equal to, or higher than five. In cases where it discovers both criteria are met, it notes down one instance and then totals them all up, outputting how many instances of both criteria being met there are in the data.

Here's how to use the Formula menu to complete this function.

1. Open the Excel document that you want to use the COUNTIFS function in and double check that all of the data is as, and where, it should be.

   

2. Select a cell where you want the COUNTIFS function results to appear.

   

3. Select the Function menu. It's the small fx logo in the upper-left of the main Window, next to the cross and tick icons.

   

4. Next to Or select a category, use the drop-down menu to select All and then in the search box, type COUNTIFS. Select the corresponding result (make sure to select COUNTIFS, rather than COUNTIF) and select OK.

   

5. In the Function Arguments window that appears, either type in the Criteria_Range1 (the beginning and end, separated by a colon) or click/tap and drag through the cells you want to use as part of the calculation. In our test sample, that's cell D4 through D17, so it's input as D4:D17.

   

6. Type or select the Criteria1 that you want the COUNTIFS function to consider. In our example, we want it to consider all Yes results in the D column, so we input Yes.

   

7. Do the same with Criteria_Range2 and Criteria2, selecting the cells and inputting the criteria you're looking for. In our example we're looking for people who have visited Lifewire five or more times, so we put in E4:E17 and >=5.

   

8. If you have additional ranges and criteria you want to consider, add them in the same way.

9. When you're finished, select OK. If you input everything correctly, you should see the result appear in the cell you performed the COUNTIF function in. In our example, the result of 6 appeared, because six people said they loved Lifewire, and visited it more than five times.

   

In this example, the above steps are repeated for people who said they didn't love Lifewire, but had still visited five or more times. That ended up being a much lower count, as you might expect, but it's still interesting data gleaned from the dataset.

These results are a little obvious from a glance with such a limited data set, but the COUNTIFS function can be used on a near infinite amount of information. The larger the dataset is, the more useful COUNTIFS functions can be in analyzing it.

If you don't need multiple ranges and criteria, you can always use the COUNTIF function instead, which is limited to just one range and criteria.

What Is the COUNTIFS Function?

Excel can be incredibly versatile by itself with manual data entry and analysis, but it's even more useful when you automate parts of it. That's where functions come in. From using SUM to make various numerical calculations, to removing non-printable characters with CLEAN. COUNTIFS works in much the same way, but, like the COUNTIF function, COUNTIFS is useful for analyzing the data. Where COUNTIF looks at a single range of data and criteria, though, COUNTIFS looks at multiples of each.

It takes these inputs and outputs a total based on what you're looking for.