COUNTIFS: Multiple Criteria Counting (2025)
Quick Answer: COUNTIFS counts with multiple AND conditions: =COUNTIFS(A:A,">100", B:B,"Active") counts where A>100 AND B=Active. Excel 2007+. For OR logic, add multiple COUNTIFS. Up to 127 criteria pairs supported.
What is COUNTIFS?
COUNTIFS counts cells that meet multiple criteria simultaneously. It's like COUNTIF but handles complex AND conditions across multiple columns. Perfect for analyzing data with multiple filters.
COUNTIF vs COUNTIFS:
COUNTIF
=COUNTIF(A:A, ">100")
One condition
Count where A > 100
COUNTIFS
=COUNTIFS(A:A,">100", B:B,"Active")
Multiple AND conditions
A > 100 AND B = "Active"
Real Example:
Question: How many sales were >$1000 in the West region during Q1?
=COUNTIFS( Sales, ">1000", Region, "West", Quarter, "Q1" )
Counts rows where ALL three conditions are TRUE
Key Characteristics:
AND Logic
All conditions must be TRUE to count
Multiple Ranges
Each criterion applies to its own range
Up to 127 Criteria
Practically unlimited conditions (127 range/criteria pairs)
Excel 2007+
Not available in Excel 2003 or earlier
💡 Common Use Cases
- • Sales analysis: Count deals by region, amount, and status
- • Inventory: Items low in stock AND expensive
- • HR analytics: Employees by department, salary range, and tenure
- • Survey data: Respondents matching demographic criteria
- • Quality control: Defects by type, severity, and date range
COUNTIFS Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments (Paired)
COUNTIFS requires pairs of range and criteria. Minimum 1 pair, maximum 127 pairs.
criteria_range1 (required)
The first range to evaluate
A2:A100 or Sales[Amount]criteria1 (required)
The condition for range1
">1000" or "Active" or B5criteria_range2, criteria2... (optional)
Additional range/criteria pairs (up to 127 pairs total)
B2:B100, "West", C2:C100, ">=2025"Criteria Syntax
Comparison Operators:
">100" - Greater than 100
"<50" - Less than 50
">=100" - Greater or equal
"<>0" - Not equal to 0
Text Matching:
"Apple" - Exact match
"App*" - Starts with "App"
"*ple" - Ends with "ple"
"?pple" - ? = any single char
Dates:
">"&DATE(2025,1,1)
">=1/1/2025"
"<"&TODAY()
Cell References:
B5 - Value from B5
">"&B5 - Greater than B5
"*"&B5&"*" - Contains B5
⚠️ Important Rules
- • Equal range sizes: All criteria ranges must be the same size and shape
- • Non-adjacent OK: Ranges don't need to be next to each other
- • AND logic only: All conditions must be TRUE (can't do OR without workarounds)
- • Case insensitive: "Apple" matches "apple" and "APPLE"
- • Wildcard support: * (multiple chars) and ? (single char) work in text criteria
- • Empty cells: Use "" to count blanks, "<>" to exclude blanks
12+ COUNTIFS Examples
Basic Multiple Criteria
1. Two Conditions
=COUNTIFS(A2:A100, ">1000", B2:B100, "West")
Count sales >1000 in West region
2. Three Conditions
=COUNTIFS(A:A,">=90", B:B,"Math", C:C,"Senior")
Seniors with Math scores >=90
3. Range Between Two Values
=COUNTIFS(A2:A100,">=100", A2:A100,"<=200")
Count values between 100 and 200 (inclusive)
4. Exclude Blanks
=COUNTIFS(A:A,">100", B:B,"<>")
A>100 AND B is not blank
Date-Based Counting
5. Current Year
=COUNTIFS(A:A,">="&DATE(YEAR(TODAY()),1,1), A:A,"<"&DATE(YEAR(TODAY())+1,1,1))
Count dates in current year
6. Last 30 Days with Status
=COUNTIFS(DateCol,">="&TODAY()-30, StatusCol,"Complete")
Completed items in last 30 days
7. Specific Month and Year
=COUNTIFS(A:A,">=1/1/2025", A:A,"<2/1/2025", B:B,"Paid")
Paid invoices in January 2025
Wildcard Patterns
8. Text Contains
=COUNTIFS(A:A,"*Apple*", B:B,">100")
Products containing "Apple" with quantity >100
9. Starts With Pattern
=COUNTIFS(A:A,"EMP-*", B:B,"Active", C:C,">50000")
Employee IDs starting with "EMP-", active, salary >50k
Advanced Techniques
10. OR Logic Workaround
=COUNTIFS(A:A,"Red", B:B,">100") + COUNTIFS(A:A,"Blue", B:B,">100")
Count Red OR Blue where B>100
11. Dynamic Criteria from Cells
=COUNTIFS(Sales, ">"&E2, Region, F2, Status, G2)
Criteria pulled from cells E2, F2, G2
12. Not Equal to Multiple Values
=COUNTIFS(A:A,"<>Pending", A:A,"<>Cancelled", B:B,">0")
Exclude Pending and Cancelled, with B>0
Common Patterns
Between Dates with Category
=COUNTIFS(Date,">="&StartDate, Date,"<="&EndDate, Category,"Sales")
Multiple Non-Blank Checks
=COUNTIFS(A:A,"<>", B:B,"<>", C:C,"<>")
This Month Only
=COUNTIFS(A:A,">="&EOMONTH(TODAY(),-1)+1, A:A,"<="&EOMONTH(TODAY(),0))
🎯 Pro Tips
- • Use tables: Structured references make formulas readable: =COUNTIFS(Table[Status],"Active")
- • Performance: COUNTIFS is fast even on large datasets (100k+ rows)
- • Error handling: Wrap in IFERROR if ranges might be invalid
- • Debug: Test each criterion separately with COUNTIF first
- • Complex OR: Use SUMPRODUCT for complex AND/OR combinations
Frequently Asked Questions
Can COUNTIFS do OR logic?
No, COUNTIFS uses AND logic only (all conditions must be true). For OR, add multiple COUNTIFS: =COUNTIFS(A:A,'Red')+COUNTIFS(A:A,'Blue') counts Red OR Blue. For complex OR/AND combinations, use SUMPRODUCT: =SUMPRODUCT((A:A='Red')+(A:A='Blue')*(B:B>100)).
Why do my criteria ranges need to be the same size?
COUNTIFS compares cells row-by-row across all ranges. If ranges are different sizes, Excel can't align the comparisons and returns #VALUE! error. Example: COUNTIFS(A1:A10,'>100', B1:B5,'Active') fails. Use A1:A10 and B1:B10 instead.
How do I count blanks with COUNTIFS?
Use empty string: =COUNTIFS(A:A,'', B:B,'>100') counts where A is blank AND B>100. Use '<>' to exclude blanks: =COUNTIFS(A:A,'<>') counts non-blank cells in A. Note: Formulas returning '' are NOT blank for COUNTIFS.
Can I use cell references in criteria?
Yes! For exact match: =COUNTIFS(A:A,E2) uses value from E2. For operators: =COUNTIFS(A:A,'>'&E2, B:B,F2) uses >E2 for A and exact F2 for B. Concatenate operators with &: '>=', '<', '<>', etc.
What's the difference between COUNTIFS and SUMPRODUCT?
COUNTIFS: Simple AND logic, faster, easier syntax. =COUNTIFS(A:A,'>100', B:B,'Active'). SUMPRODUCT: Complex logic (AND/OR), slower, powerful. =SUMPRODUCT((A:A>100)*(B:B='Active')+(C:C='VIP')). Use COUNTIFS first, SUMPRODUCT for complex needs.
Why is my COUNTIFS returning 0 when data exists?
Common causes: (1) Extra spaces in data - use TRIM to clean. (2) Numbers stored as text - use VALUE. (3) Case sensitivity - COUNTIFS is case-insensitive, but wildcards matter. (4) Wrong range sizes. (5) Criteria syntax error - operators need quotes: '>100' not >100.