Quick Navigation
COUNTIFS Function
Summary
The COUNTIFS function counts the number of cells within multiple specified ranges that meet all given criteria simultaneously. It extends COUNTIF functionality to handle complex multi-condition counting scenarios across corresponding cell positions.
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| criteria_range1 | Range |
Yes | Required first range for evaluation |
| criteria1 | Criteria |
Yes | Required criteria matching cells in first range |
| criteria_range2 | Range |
No | Optional additional ranges (must match criteria_range1 dimensions) |
| criteria2 | Criteria |
No | Optional criteria for additional ranges |
Using the COUNTIFS Function
COUNTIFS excels at multi-criteria counting across multiple ranges. Each criteria_range must have identical dimensions, and the function counts only when ALL corresponding cells meet their respective criteria simultaneously.
Common COUNTIFS Examples
Count Sales Quota Achievements
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")Counts salespeople who exceeded both Q1 and Q2 quotas (result: 2)
Count Numbers in Date Range
=COUNTIFS(A2:A7,"<6",A2:A7,">1")Counts numbers between 2-5 in range A2:A7 (result: 4)
Count Records Before Specific Date
=COUNTIFS(A2:A7,"<5",B2:B7,"<5/3/2011")Counts rows where number <5 AND date before May 3 (result: 2)
Using Cell References
=COUNTIFS(A2:A7,"<"&A6,B2:B7,"<"&B4)Dynamic criteria using cell references for flexible counting
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: Ranges have different dimensions
Solution: Ensure all criteria ranges match exactly in size
Incorrect count results
Cause: Criteria mismatch or range misalignment
Solution: Verify range alignment and criteria logic
Empty criteria treated as 0
Cause: Criteria references empty cells
Solution: Use explicit criteria or check cell contents
Notes
- Counts only when ALL criteria pairs match simultaneously
- Supports logical operators: >, <, >=, <=, =, <>
- Wildcards ? (single char) and * (sequence) work in text criteria
- Ranges don't need to be adjacent
- Available since Excel 2007
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+