Excel COUNTIFS Function (Count With Multiple Conditions) – Examples & Practice
Practice the Excel COUNTIFS function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Count rows where region in A2:A6 is "South" and value in C2:C6 is greater than 300.
Formula Syntax
=COUNTIFS(criteria_range1, criteria1, ...)
- criteria_range1: First range to evaluate.
- criteria1: First criterion.
- ...: Additional criteria_range / criteria pairs.
What it does
COUNTIFS counts rows that satisfy multiple criteria at once. All tests must pass on the same row (AND logic).
Excel COUNTIFS Function Examples
Two conditions
=COUNTIFS(A2:A6, "South", C2:C6, ">300")
Counts rows where both tests pass.
sales-by-region.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | North | Liam | 320 |
| 3 | South | Mia | 280 |
| 4 | North | Noah | 410 |
| 5 | West | Olivia | 295 |
| 6 | South | Paul | 360 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Mirror the same pattern as SUMIFS for mental model consistency.
- Keep criteria cells visible for auditability.
- Prefer Excel Tables for growing data.
COUNTIFS Function Use Cases
- Multi-segment funnel counts
- Quality defect tallies across lines
- Operational compliance checks
- Dashboard slice counters
- Interview-style criteria drills
Common mistakes - COUNTIFS function not working
- Misaligned ranges across pairs
- Expecting OR logic inside one COUNTIFS
- Mixing headers into criteria ranges
- Text vs numeric threshold mistakes
- Overusing whole-column references
FAQ
What is the argument pattern?
COUNTIFS takes repeating pairs: criteria_range1, criteria1, criteria_range2, criteria2, ...
COUNTIFS vs SUMIFS argument order?
COUNTIFS has no sum_range; it is all criteria pairs.
Why zero matches?
AND logic requires all criteria true on the same row—check data alignment.
Can I do OR?
Add multiple COUNTIFS results or use helper columns / modern array patterns.
Does COUNTIFS work with dates?
Yes, with proper serial comparisons.
Comparison
| Function | Criteria |
|---|---|
| COUNTIFS | Multiple |
| COUNTIF | One |
| SUMIFS | Sums instead of counting |
Example
=COUNTIFS(A2:A6, "South", C2:C6, ">300")
Advanced examples
Month-to-date counts
Combine COUNTIFS with start/end date cells built from DATE/EOMONTH for rolling reporting windows.
Related functions
COUNTIF is the single-condition special case—keep it when extra ranges would only add noise.
Align criteria with SUMIFS and AVERAGEIFS so one documented filter block drives an entire metrics panel.