📎 This article includes 2 downloadable practice files ↓
In this article
COUNTIF counts cells that meet one condition. It is also the simplest duplicate checker.
Syntax
=COUNTIF(range, criteria)| Argument | What it means |
|---|---|
range |
Cells to check. |
criteria |
Condition, e.g. “Paid”, “>=50000”, “*@gmail.com”. |
Examples
Example 1
=COUNTIF(C:C, "Overdue")Number of overdue invoices.
Example 2
=COUNTIF($A$2:$A$500, A2)>1TRUE for duplicate IDs — use it in conditional formatting.
COUNTIF in practice
Where you will use it
- How many invoices are overdue
- Count students who scored 90+
- Find duplicates: COUNTIF(A:A,A2)>1
Worked example
How many times each customer appears (duplicate check).
| Customer | Formula | Result |
|---|---|---|
| Patel | =COUNTIF($A$2:$A$4,A2) | 2 |
| Shah | =COUNTIF($A$2:$A$4,A3) | 1 |
| Patel | =COUNTIF($A$2:$A$4,A4) | 2 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Criteria without quotes | Write ">90" in quotes |
| Wildcards in data | ~* and ~? match a real * or ? |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
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.
Stuck on a step? Ask a question and the AI answers using this article.