How to Use Excel COUNTIFS: The Best Guide
This COUNTIFS in Excel tutorial is suitable for users of Excel 2010/2013/2016/2019 and Microsoft 365.
Objective
Use the Excel COUNTIFS function to count the number of cells in a range that match one or more criteria.
Excel 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. Excel 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.
Video Tutorial – Using COUNTIFS in Excel
To see COUNTIFS in action, please watch the following video tutorial.
Related:
Logical Functions In Excel (If, Ifs, And, Or, Countif, Sumif)
Advanced Formulas In Excel – 1 Hour Recorded Webinar
Excel Sumifs & Sumif Functions – The No.1 Complete Guide
How to Use COUNTIFS in Excel?
Let’s break down this guide into easy steps for clarity.
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.
Also Read:
How To Protect Cells In Excel Workbooks-the Easiest Way
Create An Excel Dashboard In 5 Minutes – The Best Guide
Dynamic Dropdown Lists In Excel – Top Data Validation Guide
Using Named Ranges in Excel 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.
Excel COUNTIFS – Let’s Roundup
The key takeaway of this guide is that the Excel COUNTIFS function is a valuable addition to the list of Excel functions, as it allows us to count based on multiple criteria at once.
If you have any doubts related to COUNTIFS or other Excel functions, you can ask them in the comments below. If you want more quality information on advanced features in Excel, check out our Excel courses.
FAQs
What is the COUNTA function in Excel?
The COUNTA function counts for cells with any information including empty text and error values. It will not count empty cells.
What’s the difference between COUNTIF and COUNTIFS?
COUNTIF is used to count values in a single range for a single criterion, whereas COUNTIFS is used to count values across multiple criteria and ranges.
Is COUNTIFS AND or OR?
COUNTIFS can be used both in AND and OR mode. It can be used in the OR mode by adding two different criteria COUNTIFS functions together.
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.
Other Excel classes you might like:
- Introduction to Power Pivot & Power Query in Excel
- What-If Analysis in Excel
- Designing Better Spreadsheets in Excel
- How to Use the SUMIF and SUMIFS Function in Microsoft Excel
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.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
