Countif Multiple Criteria

Countifs function in Excel to count cells where multiple criteria are met

Written by CFI Team

Read Time 2 minutes

Join over 1.8 million professionals who trust CFI for their learning needs in accounting, financial analysis, modeling, and more. Kickstart your journey with a free account to access 20+ always-free courses and explore a treasure trove of finance resources, including hundreds of templates, cheat sheets, and specialized content like Countif Multiple Criteria tutorials. Start Free

The following guide on the COUNTIF function for multiple criteria in Excel explains how to use the COUNTIFS function to count cells that meet multiple conditions. The COUNTIFS function allows you to specify multiple criteria ranges and conditions, making it more versatile than COUNTIF. The tutorial below provides step-by-step instructions, examples, and practical applications in financial analysis, such as selecting companies based on multiple financial metrics.

How to Countif Multiple Criteria?

The Excel formula to countif multiple criteria[1] is =countifs(). The “s” on the end makes it plural and, therefore, implies that there are multiple criteria (2 or more).

Using the examples and illustrations below, this tutorial will show you exactly how to use COUNTIFS in Excel.

How to use countif multiple criteria

Countifs works just like countif, except you can add additional criteria separated by commas. Here is step-by-step instructions on how to do it.

Step 1: Document the criteria or conditions you wish to test for.
Step 2: Type “=countifs(“ and select the range you want to test the first criteria on.
Step 3: Input the test for the criteria.
Step 4: Select the second range you want to test (it can be the same range again, or a new one).
Step 5: Input the test for the criteria.
Step 6: Repeat above as many times as necessary.
Step 7: Close “)” and press enter.

Example of countif with multiple criteria

This screenshot shows counting with multiple conditions in action. As you will see, each of the criteria is being tested, and Excel is counting the number of times the result from the test is true. This is how to use Excel countif multiple criteria:

Download the Free Template

Enter your name and email in the form below and download the free template now!

Why use countif?

There are many instances where you might want to count the number of times multiple criteria are met. In finance, this can be used to select companies that fit a certain profile. As in the case above, we wanted to find out how many companies were over a certain Market Cap, but below a certain EV/EBITDA multiple and below a certain PE ratio.

Video Tutorial – COUNTIFS Function in Excel

To learn more about how to use the COUNTIFS Function in Excel, check out the video below:

More Excel formulas and functions

Thank you for reading the CFI guide to countif multiple criteria in Excel. If you want to become an Excel power user, we’ve generated a large Excel resources database of all the most important formulas and functions, Excel shortcuts, and general best practices.

Some of our most useful spreadsheet resources include:

Article Sources