Summary
The Excel COUNTIFS function counts the number of cells in a range that meet multiple criteria. It’s great for when you need to count cells that match several conditions at once, like finding the number of sales in a particular region that exceed a certain amount.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- criteria_range1: The first range of cells to evaluate with the first condition.
- criteria1: The condition that cells in the first range must meet.
- [criteria_range2, criteria2], …: Additional ranges and their corresponding conditions. You can specify up to 127 pairs.
Return value
The number of cells that meet all the given conditions.
How to use
Input the ranges and their corresponding conditions. Each pair of range and condition adds a new layer of criteria, narrowing down the cells that are counted based on all the specified conditions.
Examples
Simple COUNTIFS
Counting Cells with Exact Numbers and Conditions: Finding entries within a specific number range.
=COUNTIFS(A1:A10, ">5", A1:A10, "<10")
If A1:A10 has four cells with numbers between 6 and 9, COUNTIFS reports 4, showing how many numbers fall into this range.
COUNTIFS for Different Criteria
Counting Based on Multiple Conditions Across Columns: Calculating the number of products with sales above a certain level in a specific region.
=COUNTIFS(B1:B20, ">500", C1:C20, "East")
If B1:B20 represents sales figures and C1:C20 regions, and there are 5 sales over $500 in the “East” region, COUNTIFS returns 5.
COUNTIFS with Date Range
Finding Entries Within a Specific Date Range: Counting sales in a particular month.
=COUNTIFS(D1:D30, ">=01/01/2022", D1:D30, "<=01/31/2022")
This counts how many entries in D1:D30 fall within January 2022. If there are 7, COUNTIFS shows 7.
COUNTIFS for Text and Numbers
Combining Text and Number Criteria: Counting how many times a specific project received a certain score.
=COUNTIFS(A1:A10, "Project X", B1:B10, ">=80")
If A1:A10 lists project names and B1:B10 contains scores, and “Project X” has 3 scores of 80 or more, COUNTIFS reports 3.
Additional Notes
- COUNTIFS is case-insensitive for text criteria.
- It allows for detailed data analysis by applying multiple filters to your data set.
- Ensure the ranges you specify are the same size to avoid errors.