Using COUNTIFS in Excel
This COUNTIFS in Excel tutorial is suitable for users of Excel 2010/2013/2016/2019 and Microsoft 365.
Objective
Use the COUNTIFS function to count the number of cells in a range that match one or more criteria.
COUNTIFS Explained
COUNTIFS is a statistical function in Excel. It differs from its closely related friend COUNTIF, as it allows you to count items in a list based on multiple criteria and ranges. The COUNTIF function only lets you count based on one condition. COUNTIFS does its job so well that it has made the original COUNTIF function almost obsolete. Do not panic, COUNTIF lovers, the COUNTIF function is still available in Excel.
Method
Let’s start by looking at the COUNTIF function.
The COUNTIF function
In this example, I want to count the number of trips launched in 2019. To do this, I select the cell range that contains the year I am looking for, and then I specify my criteria, 2019.
Syntax
COUNTIF(range, criteria)
Using COUNTIF, I can search in one range for one piece of criteria.
The COUNTIFS function
In this example, I am using COUNTIFS to count the number of trips launched in ‘2019’ that have the activity level of ‘Relaxed’.
- Type =COUNTIFS into a cell
- Select the criteria range 1, B4:B14 (the range that contains what you are looking for)
- Type comma
- Enter “2019” in quote marks as the criteria
- Type comma
- Select the criteria range 2, C4:C14
- Type comma
- Enter “Relaxed” in quote marks as the criteria2
- Press Enter
The result is 2.
You can specify up to 127 range/criteria pairings in your formulas.
Logical operators can also be used.
In this example, I am using the greater than (>) logical operator to count the number of trips in 2017 with a price higher than $1200.
Using Named Ranges in COUNTIFS
It’s good to get into the habit of naming cell ranges. When you name a range, it gives meaning and helps others understand what cells you are referring to in formulas.
Naming a Range
- Select the cell range you wish to name
- In the Name box above, type in a meaningful name for the range
- Press Enter
These named ranges can now be used in the COUNTIFS formula instead of the cell ranges.
Like what you see? Check out these links for more COUNTIFS examples and if you are feeling brave, why not try using COUNTIFS with a dynamic criteria range.
Exceljet – Excel COUNTIFS function
Trump Excel – How to Use Multiple Criteria in Excel COUNTIF and COUNTIFS Function
ExcelTip – Get the COUNTIFS with Dynamic Criteria Range in Excel
Video Tutorial
To see COUNTIFS in action, please watch the following video tutorial.
For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.
To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.
