The COUNTIFS function is a powerful Excel function that counts rows based on multiple conditions. Unlike COUNTIF, which handles a single condition, COUNTIFS allows you to apply multiple criteria to your dataset. This makes it essential for advanced data analysis and precise filtering.
COUNTIF vs. COUNTIFS
This table shows the differences between the COUNTIF and COUNTIFS functions. There is no huge differences except the number of criteria.
| Feature | COUNTIF | COUNTIFS |
|---|---|---|
| Conditions | Single condition only | Multiple conditions supported (max 256 conditions) |
| Use Case | Count one type of data | Analyze complex datasets |
| Example | Count “East” in Area column | Count “Banana” in “West” region |
COUNTIFS Syntax
- criteria_range1: The first range to evaluate.
- criteria1: The condition for the first range.
- criteria_range2: The second range to evaluate.
- criteria2: The condition for the second range.
- Additional ranges and conditions can be added.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
Examples with Multi-Criteria
The following document represents the sales of fruits per Region for different dates.
#1: Number of sales of Apricot in the East region
- Select the column with the fruits.
- Write your criteria “Apricot”
- Select the range of the second criteria, the Area
- Write the criterion “East”
#2: Number of Cherry sales the 09/08/2024
We will select the date and fruit columns with the same logic.
Remarks:
- It doesn’t matter for the function the order of selection of the columns.
- For a date, you can write it as a string or with the DATE function
DATE(2024,8,9)
#3: Amount of Sales greater than 20 for the North Region
This time, we will include in Sales criteria, the symbol greater to select all the sales greater than 20.
- By default, when you write the name of your criteria, it’s like if you write “=North”
- So you can perferctly write any logical operator in your criteria
Why Use COUNTIFS?
- Automates filtering for large datasets.
- Reduces errors from manual counting.
- Saves time in multi-condition analysis.
Start using COUNTIFS to handle complex data analysis with ease!
