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:

Objective
Excel COUNTIFS Explained
Video Tutorial – Using COUNTIFS in Excel
How to Use COUNTIFS in Excel?
- The COUNTIF function
- The COUNTIFS function
- Using Named Ranges in Excel COUNTIFS
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.

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.

The COUNTIF Function
The COUNTIFS Function
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 (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 (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.

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

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:

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.