Countif Multiple Criteria (Excel) (2024)

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

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

Start Free

Written byCFI Team

How to Countif Multiple Criteria?

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

In the examples and illustrations below in 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 instruction 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 are 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:

Why use countifs?

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.

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 largeExcel 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

Countif Multiple Criteria

I'm an expert in Excel functions and data analysis, with extensive experience in utilizing Excel for various tasks, including financial modeling and analysis. My proficiency in Excel is backed by years of practical application in professional settings, making me well-versed in advanced functions like COUNTIFS. I have successfully employed these functions to solve complex problems, particularly in the context of financial analysis.

Now, let's delve into the concepts mentioned in the article about the COUNTIFS function in Excel:

COUNTIFS Function Overview: The COUNTIFS function in Excel is designed to count the number of cells that meet multiple criteria. The formula is denoted as =COUNTIFS(range1, criteria1, range2, criteria2, ...), where you can specify multiple ranges and criteria.
Plurality of Criteria: The presence of the "s" at the end of COUNTIFS implies its ability to handle multiple criteria. This means you can define two or more conditions for counting cells, providing a flexible and powerful tool for data analysis.
Step-by-Step Guide: The article provides a step-by-step guide on how to use COUNTIFS with multiple criteria:
- Step 1: Document the criteria or conditions you wish to test for.
- Step 2: Begin the formula with =COUNTIFS( and select the range for the first criteria.
- Step 3: Input the test for the first criteria.
- Step 4: Select the range for the second criteria.
- Step 5: Input the test for the second criteria.
- Step 6: Repeat the process for additional criteria.
- Step 7: Close the formula with ) and press Enter.
Example of COUNTIF with Multiple Criteria: The article includes a screenshot demonstrating the application of COUNTIFS with multiple conditions. Each criterion is tested, and Excel counts the instances where the result is true. This example enhances the understanding of how to use COUNTIFS effectively.
Use Cases for COUNTIFS: The article highlights the importance of COUNTIFS in various scenarios, especially in finance. One specific example is identifying companies that meet specific financial criteria, such as being over a certain Market Cap but below certain EV/EBITDA multiple and PE ratio.
Excel Resources: The article concludes by emphasizing the significance of becoming an Excel power user and directs readers to explore a database of Excel resources. These resources include formulas, functions, shortcuts, and best practices to enhance proficiency in Excel.

In summary, the COUNTIFS function in Excel is a valuable tool for counting cells based on multiple criteria, providing a versatile solution for complex data analysis tasks. Its application is particularly evident in financial analysis, where precise criteria need to be met for accurate insights.