📎 This article includes 2 downloadable practice files ↓
In this article
COUNTIFS counts rows where every condition is true.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | What it means |
|---|---|
criteria_range1, criteria1 |
First range and condition. |
Examples
Example 1
=COUNTIFS(B:B, "North", E:E, ">=50000")Big North orders.
Example 2
=COUNTIFS(A:A, ">="&G1, A:A, "<="&G2)Rows between two dates.
COUNTIFS in practice
Where you will use it
- Orders from North above ₹10,000
- Employees in Pune with rating 4+
- Tickets open more than 7 days for one team
Worked example
Orders above ₹10,000 in the same region as the row.
| Region | Amount | Formula | Result |
|---|---|---|---|
| North | 15000 | =COUNTIFS($A$2:$A$4,A2,$B$2:$B$4,">10000") | 1 |
| North | 8000 | =COUNTIFS($A$2:$A$4,A3,$B$2:$B$4,">10000") | 1 |
| South | 22000 | =COUNTIFS($A$2:$A$4,A4,$B$2:$B$4,">10000") | 1 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Ranges of different sizes | All ranges must have the same rows |
| Expecting OR logic | COUNTIFS is AND; add two COUNTIFS for OR |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 38 KB)
- 📗SUMIFS/COUNTIFS scenarios workbookConditional sums and counts with dates, OR logic and wildcards, plus 4 classic reasons totals come out wrong — all with PASS/FAIL checks.⬇ XLSX · 9 KB
- 📗Practice workbook: Statistics basics22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 34 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong