Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > How to Use Excel COUNTIFS: The Best Guide

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.  

Table of Contents:

  1. Objective 
  2. Excel COUNTIFS Explained 
  3. Video Tutorial – Using COUNTIFS in Excel
  4. How to Use COUNTIFS in Excel?
    • The COUNTIF function 
    • The COUNTIFS function 
    • Using Named Ranges in Excel COUNTIFS 
  5. Excel COUNTIFS – Let’s Roundup

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. 

  1. The COUNTIF Function
  2. The COUNTIFS Function
  3. Using Named Ranges in COUNTIFS

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) 

COUNTIF Function - Excel COUNTIFS
To count the number of occurrences of a specific case, select the range that contains the case and specify the criteria inside the COUNTIF function

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’. 

COUNTIFS Example
To count the number of occurrences of cases with multiple criteria, specify the range of cells for each case, followed by its respective criterion inside the COUNTIFS function.
  • 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.  

COUNTIFS with 2 Criteria
COUNTIFS with 2 Criteria (Activity level “Relaxed” and Trips launched in the year 2019)

You can specify up to 127 range/criteria pairings in your formulas.  

COUNTIFS with 3 Criteria
COUNTIFS with 3 Criteria (Trips Launched in 2018, in Australia and Price of $4000)

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.  

COUNTIFS with Logical Operators
To use COUNTIFS with Logical Operators, simply insert the logical statement in quotes after the relevant cell range

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 
Naming a Range
Select the cell range you wish to name
Type a meaningful name for named ranges
Type a meaningful name in the name box

These named ranges can now be used in the COUNTIFS formula instead of the cell ranges.  

Named Ranges inside COUNTIFS function
Use these named ranges directly inside the COUNTIFS function

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.

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

Similar Posts

Asana vs. Jira: The 6 Key Differences

Combine numbers and words in Excel 2013 using the VLOOKUP Function

Advanced PowerPoint Hacks 2022 – (Recorded Webinar)

How to Use the Format Painter Excel Feature? — 3 Bonus Tips

How to Round Up in Excel(and Round Down)?

How to Add a Watermark in Excel? 2 Easy Methods

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)