COUNTIF in Excel: Formula Examples for Budgets, Status, and Duplicates
Use COUNTIF in Excel to count rows by category, status, threshold, duplicate value, or wildcard match. Includes practical formulas and fixes.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
COUNTIF counts how many cells in a range meet one condition. It is the formula behind common review questions like "how many expenses are software?", "how many tasks are overdue?", "how many rows are blank?", or "how many duplicate IDs appeared in this export?".
That makes it useful in live operating sheets like a small business budget, budget tracker, or expense tracker where counts by category or status should update automatically.
The syntax
=COUNTIF(range, criteria)- range — the cells to check
- criteria — what to count for. Can be text, a number, a comparison, a wildcard, or a cell reference
Common use cases
Count by exact text match:
=COUNTIF(A2:A100, "California")Count numbers meeting a threshold:
=COUNTIF(B2:B100, ">1000")Count non-blank cells:
=COUNTIF(A2:A100, "<>")Count cells containing specific text (wildcard):
=COUNTIF(A2:A100, "*Corp*")The * wildcard matches any sequence of characters. Use ? to match exactly one character.
Count using a cell reference as criteria:
=COUNTIF(A2:A100, D2)✦ TIP
To count duplicates, use =COUNTIF($A$2:$A2, A2) with an expanding range. Any result greater than 1 is a duplicate. See the remove duplicates guide for the full pattern.
COUNTIF is case-insensitive
"apple", "Apple", and "APPLE" all count as the same. If case matters, use EXACT inside SUMPRODUCT:
=SUMPRODUCT((EXACT(A2:A100, "Apple"))*1)Multiple conditions: COUNTIFS
COUNTIF only handles one condition. For two or more, use COUNTIFS:
=COUNTIFS(A2:A100, "California", B2:B100, ">1000")COUNTIFS uses the same pattern as SUMIFS — alternate between range and criteria pairs.
Common mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Forgetting quotes around text | #NAME? error | Wrap text in quotes: "California" |
| Forgetting quotes around operators | Counts wrong | Use ">1000" not >1000 |
Using = instead of "" for blanks | Misses some blanks | Use "" for blanks, "<>" for non-blanks |
The Griddy way
COUNTIF is easy for simple counts but awkward for partial matches, case sensitivity, or OR logic across multiple columns. Just ask:
"Count how many rows have 'Corp' anywhere in the company name and a deal value over $5,000"
Griddy writes the formula and applies it — no manual syntax construction.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Use COUNTIF on live finance sheets
COUNTIF shows up constantly in budgeting and expense review when you need fast counts by category, status, or threshold.
Small Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Small Business Budget for Service Businesses
Plan service revenue, payroll, contractors, scheduling costs, supplies, software, marketing, and operating margin in one budget spreadsheet.
Budget Tracker
Track income, expenses, and savings in one place. Line items, budgeted vs actual totals, and monthly net savings — free to use in your browser.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.