What is the COUNTIFS Function in Excel?
Excel “COUNTIFS” function counts the supplied range of values based on more than one criteria. In our earlier article, we have discussed how to use the “COUNTIF” function. “COUNTIF” function counts based on single criteria.
For example, look at the below data for the sample.
In the above data, if we want to count how many products of “B” are there in the region “East”. Here we not only need to count only product “B” but also in the region “East”.
This is where the COUNTIFS function helps us to count based on multiple conditions.
Syntax
Let’s look at the COUNTIFS formula in excel.
- Criteria Range 1: What is the first range we need to count?
- Criteria 1: What do we need to count from the criteria range 1?
- Criteria Range 2: What is the second range we need to count?
- Criteria 2: What do we need to count from the criteria range 2?
Like this, we can supply up to 127 range or criteria’s combination in these formulas. Now we will see examples of COUNTIFS function in excel.
How to Use COUNTIFS Function with Multiple Criteria in Excel?
Below are some of the examples of COUNTIFS formula in excel.
Example #1
Let’s have look at the simple example of COUNTIF formula with excel. For this example look at the below data.
From the above list, we need to count the product “B” in the region “East”.
Let’s open the COUNTIFS formula first.
Criteria Range 1 is the first range of cells that we need to count, lets select Region first.
Note: You can select the Product also.
Next argument is Criteria 1 so in the select range of cells (Criteria Range 1) what do we need to count??? i.e. “East”.
Third criteria are Criteria Range 2 for this select product range of cells.
Next argument is Criteria 2 i.e. what do we need to count in the selected Criteria Range 2. In this case, we need to count “B”.
Ok, that’s all.
Hit enter key, we will have the count of Product “B” for the region “East”.
So, for the region “East” we have 2 product “B” count.
Even though we have another count of Product “B” in cell B12, the formula has ignored this because the region for that product is “West” not “East”.
Example #2
For the previous example data, I have added one extra column i.e. Sales Person. From this table, we need to count what is the total count of product “B” in the region “East” for the salesperson “Ranya”.
For this example look at the below data.
Here we need to look at three criteria’s which one more than the previous example. So with the continuation of the formula from the above example.
For the Criteria Range 3 select the range as “Sales Person” cells.
In the Criteria 3, we need to count for the sale person “Karan”.
Ok, that all we will have a count for the salesperson “Karan” for the region “East” for the product “B”.
We have a count of product “B” for the region “East” is two but for the salesperson “Karan” this is only 1, so our formula has the same count.
Example #3 – COUNTIFS with Logical Operators
We can use logical operators with the COUTNIFS formula to structure our criteria. Logical operators are Greater than (>), less than (<), and equal to (=).
For this example look at the below data.
From this data, we need to get the count of products in the region “East” if the Price is greater than 20.
Open COUNTIFS formula.
Select the first Criteria range 1 as region column.
Mention the criteria as “East”.
Next, select the Price column as Criteria Range 2.
For this range, our criteria are two counts if the price is >20. So mention the criteria with double quotes as “>20”.
That’s all hit enter key we will have a count of products where the price is >20 for the region “East”.
Example #4
Let’s look at one more example where we can use two operator logical symbols. For this example look at the below data structure.
In this data, we need to count how many invoices are sent between 20-Jun-2019 to 26-Jun-2019.
Open COUNTIFS function.
Select the Criteria Range 1 as an Invoice Date column.
Now the criteria are we need to count invoices between 20-Jun-2019 to 26-Jun-2019. So, mention the greater than symbol (>).
Since we have already had the date in cell put ampersand symbol and select the D2 cell.
Now again for Criteria range 2 select the range as Invoice Date only.
This time we need the count of invoices below the date of 26-Jun-2019. So, mention less than symbol in double-quotes and select D3 cell.
So invoices sent between 20-Jun-2019 to 26-Jun-2019 is 3.
Like this, we can use the COUNTIFS function in excel to count based on several applied criteria.
Recommended Articles
This has been a guide to COUNTIFS Function in Excel. Here we discuss how to use COUNTIFS formula in excel with multiple criteria along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
