COUNTIFS with multiple criteria: how to use it

Use COUNTIFS with multiple criteria like a pro and expand your Excel knowledge with Ted Jordan. Learn some tips and tricks to save time and to avoid errors.

You just discovered the COUNTIF function in Excel but need to use it with multiple conditions? Use COUNTIFS with multiple criteria!

In MS Excel, COUNTIFS is a function that applies multiple criteria to cells and counts the number of times all criteria are met.

Are you ready to learn how to use COUNTIFS?

When you need to check if some cells met multiple criteria in Excel use COUNTIFS.

The COUNTIFS function contains 2 required arguments which correspond to the required arguments in COUNTIF.

The syntax of this handy function is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

criteria_range1 – the 1st range (cells) in which you want to check the associated criteria (criteria1).
criteria1 – an element determining which cells are counted when using COUNTIFS.
criteria_range2, criteria2 – additional ranges of cells and their corresponding criteria.

COUNTIF vs COUNTIFS: which one to use?

Use COUNTIF or COUNTIFS when you want to count how many cells are meeting one specific criteria; use COUNTIFS when multiple criteria need to be checked across cells and counted.

Need a refresher about COUNTIF?

When using COUNTIFS with multiple criteria in Microsoft Excel, read and follow these tips to avoid unwanted results:

  • make sure additional ranges have the same number of cells, rows and columns as criteria_range1;
  • enclose criteria arguments in quotes “” when they contain something else than a cell reference or &;
  • use wildcard characters for text elements to save time;
  • know that COUNTIFS is not case sensitive;
  • only if all cells meet their associated criteria, for all criteria, COUNTIFS will add 1 to the count;
  • the order in which cells are displayed is important when counting with COUNTIFS: for example, if the 1st cell of the first range meet the 1st criteria but not the 2nd criteria, the result will be 0.

What are wildcard characters in Excel?

Use a question mark ? to match any single character and an asterisk * to match any sequence of characters.

For example, “exce?” will recognise “excel” while “exce*” will count “excel“, “excellent“, etc.

To help you understand more easily how COUNTIFS works in Excel, we are sharing some examples using COUNTIFS with multiple criteria: text, date, numbers, etc.

In this example, we want to check how many times a row contains “Marie”, “5” and “TRUE” in rows 22 to 24.

The formula we use is =COUNTIFS(M22:M24,”=marie”,N22:N24,”=5″,O22:O24,TRUE)

You may have noticed that “TRUE” is not enclosed with quotations marks. We could have used the formula =COUNTIFS(M22:M24,”=marie”,N22:N24,”=5″,O22:O24,”=TRUE”) for example.

In Excel, “FALSE” and “TRUE” are recognised by the system automatically so you can skip the quotation marks and the equal symbol in this particular example.

In this COUNTIFS example there are 3 criteria: one criteria contains a date, one contains a wildcard character and one contains some text.

We want to count how many times cells contain any customer’s name starting with the letter “O” and who live in North America, and who received an invoice before 2024.

The formula we used is =COUNTIFS(A18:A22,”=O*”,C18:C22,”=North America”,E18:E22,”<01/01/2024″); the result is 2.

In this example, we want to know how many clients ordered more than one time in Q3 and more than 2 times during Q4. We decide to use COUNTIFS with multiple criteria: the 1st criteria is “>1” and the 2nd criteria is “>2”.

The COUNTIFS formula we use is =COUNTIFS(D22:D37,”>1″,E22:E37,”>2″); the result is 6.

Learn how to use COUNTIFS with cells reference and other mathematical symbols by checking examples from our COUNTIF tutorial. Click here to access the free tutorial.

Now, you know how to use COUNTIFS with multiple criteria containing numbers, dates, texts, wildcard characters and much more. Don’t waste your Excel knowledge: get Excel certified today!

Join our Online Course: learn at your own pace, receive support from Excel experts, access videos anytime, anywhere.

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

Advertising News – 09 June 2024

This week, we'll explore Google's latest algorithm update and it's massive impact on independent websites, the newly passed media Freedom Act in the EU aimed at protecting journalists, and a...

Read More

Categories

AllFunctionsToolsShortcut Keys
Split Semicolon Separated Value in Excel

How to split semicolon separated values in Excel

Split semicolon separated values in Excel into columns and rows, or convert columns to semicolon separated values, like an Excel Master!

Read More

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Using Text to Column to Extract texts on Excel

Excel: How to split text into columns

If you need to extract data in Excel, you may want to use the Text to Columns tool. With only 3 steps, it's really easy to split text into columns.

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More