COUNTIFS Function in Excel: A Comprehensive Guide

If you are working on data as a finance guy or as a admin in office, you should heard Countifs function probably. Because one of the essential functions is COUNTIFS Function in Excel. Because rather than simply counting what you have, you can count the cells to meet multiple criteria.

What is the COUNTIFS Function in Excel?

The COUNTIFS function is a default function. And it basically counts the number of cells based on what you need. Good news is, it can work with multiple criteria. It means you can find results with two or more conditions. The COUNTIFS syntax is as follows.

=COUNTIFS(range1, criteria1, [range2], [criteria2],…)

Where:

• range1 is the first range to be evaluated.
• criteria1 is the criteria to be met in range1.
• range2 (optional) is the second range to be evaluated.
• criteria2 (optional) is the criteria to be met in range2.

We can include up to 127 ranges and criteria in the COUNTIFS function.

How to Use excel countifs

Using the COUNTIFS function is easy but we need to follow the syntax. ALso, we need to provide the necessary ranges and criteria. Let’s take an example below.

If we suppose we have a dataset of employees. And we want to count the number of employees who are from the Sales department. Also, they should have a salary greater than $50,000. We can use the COUNTIFS functionfor this.

excel countifs criteria

countifs syntax

=COUNTIFS(Department,”Sales”, Salary,”>50000″)

Here, Department and Salary are the named ranges in the dataset. The first criteria are to count the cells where the Department is Sales. While the second criteria to count the cells if Salary is greater than $50,000.

Using Wildcards in the COUNTIFS Function

We can also use wildcards in the COUNTIFS function for a specific pattern. The asterisk (*) is a wildcard character. And it matches any number of characters. While the question mark (?) matches a single character.

In case you have a dataset of products and we want to count the number of products that start with the letter A. We can use the COUNTIFS function with a wildcard.

=COUNTIFS(Products,”A*”)

Here, Products is range in the dataset. The criteria are to count the cells with product name starts with the letter A.

How to use countifs function? Using Logical Operators

We can use logical operators in the COUNTIFS function to find ones that meet multiple conditions. The logical operators are below.

• Equal to (=)
• Not equal to (<>)
• Greater than (>)
• Less than (<)
• Greater than or equal to (>=)
• Less than or equal to (<=)

Example below:

You have students and we want to count the number of students who scored more than 80 in Math. But also the ones scored less than 70 in English. We can use the COUNTIFS function with logical operators.

=COUNTIFS(Math,”>80″, English,”<70″)

Here, Math and English are named. The first criteria are to count the cells where the score in Math is greater than 80. Also, the second criteria is counting the cells where the score in English is less than 70.

How to use countifs with multiple criteria?

We can use multiple criteria in the COUNTIFS function. Here, let’s suppose we have a dataset of sales transactions. But we want to count transactions where the product is Product A and the salesperson is either John or Jane. We can use the COUNTIFS function with multiple criteria.

=COUNTIFS(Product,”Product A”, Salesperson,{“John”,”Jane”})

Here, Product and Salesperson are names. The first criteria counts the cells where the product is Product A. Then, the second criteria count if salesperson is either John or Jane.

Using Cell References in the COUNTIFS Function

We can also use cell references in the COUNTIFS function to make it more flexible.

Now for this example, we have dataset of orders. And we want to count the number of orders where the product is equal to the product in cell A1.

=COUNTIFS(Product,A1)

Using the COUNTIFS Function with Dates

We can also use the COUNTIFS function with dates.

Again we will imagine having lots of order. This time, we want to count the number of orders placed between 1-Jan-2022 and 31-Dec-2022. We can use the COUNTIFS function with dates.

countifs syntax

=COUNTIFS(OrderDate,”>=1-Jan-2022″, OrderDate,”<=31-Dec-2022″)

Here, OrderDate is the name for dataset. The first criteria will count if order date is greater than or equal to 1-Jan-2022. Then, second criteria will count cells when date is less than or equal to 31-Dec-2022.

Using the COUNTIFS Function with Multiple Criteria and Dates

We can combine multiple criteria and dates in the COUNTIFS function for multiple conditions.

In case we have lots of sales transactions and we want to count transactions where the product is Product A and the salesperson is either John or Jane. As a third condition, the transaction date is between 1-Jan-2022 and 31-Dec-2022. We can use the COUNTIFS function with multiple criteria and dates.

What is the Countif formula example?

=COUNTIFS(Product,”Product A”, Salesperson,{“John”,”Jane”}, TransactionDate,”>=1-Jan-2022″, TransactionDate,”<=31-Dec-2022″)

Here, Product, Salesperson, and TransactionDate are all names in dataset. First part counts cells where the product is Product A. However, the second criteria will count in case salesperson is either John or Jane. As a next step tough, the third criteria will look at ones with transaction date is between 1-Jan-2022 and 31-Dec-2022.

Conclusion on excel countifs function

The COUNTIFS function is quite flexible for counting cells based on multiple criteria. For multiple condition cases, you can use wildcards, logical operators, multiple criteria, cell references and dates. So, you have many many options to customize the function. Up until now, you got most of COUNTIFS function. But please don’t hesitate to reach out with any feedback or suggestions for future topics. We are always looking for ways to offer most valuable resources to our readers.