COUNTIF Function in Excel: Complete Guide (2025)
Quick Answer: COUNTIF counts cells matching criteria: =COUNTIF(range, criteria). Examples: =COUNTIF(A:A, ">100") counts values >100. =COUNTIF(A:A, "Apple") counts text. Wildcards: =COUNTIF(A:A, "*test*") finds containing 'test'.
What is COUNTIF?
COUNTIF counts cells in a range that meet a single condition. It's perfect for counting how many times something appears, how many values are above/below a threshold, or matching text patterns.
Simple Example:
=COUNTIF(A1:A10, ">50") // Counts how many cells in A1:A10 are greater than 50
Count Numbers
=COUNTIF(A:A, ">100")
Count values greater than 100
Count Text
=COUNTIF(A:A, "Apple")
Count cells containing "Apple"
Count Blanks
=COUNTIF(A:A, "")
Count empty cells
💡 Common Use Cases
- • Inventory: Count items below reorder level
- • Grading: Count students who passed/failed
- • Sales: Count orders above target amount
- • Data quality: Count blank or error cells
- • Text matching: Count specific words or patterns
COUNTIF Syntax
=COUNTIF(range, criteria)
1. range (required)
The cells to count. Can be a column, row, or range.
A1:A100 ← Single columnA:A ← Entire columnB2:D10 ← Multi-column range2. criteria (required)
The condition cells must meet to be counted.
">50" ← Number comparison"Apple" ← Exact text match"*test*" ← Wildcard patternComparison Operators
>Greater than<Less than>=Greater than or equal
<=Less than or equal=or""Equal to<>Not equal to
Wildcards
* (asterisk) = Any number of characters
Example: "*apple*" matches "pineapple", "apple pie", "green apple"
? (question mark) = Single character
Example: "b?t" matches "bat", "bit", "but" (not "boot")
~ (tilde) = Escape wildcard
Example: "~*" matches literal asterisk character
15+ COUNTIF Examples
1. Count Greater Than Value
=COUNTIF(A2:A100, ">50")
Counts cells in A2:A100 where value is greater than 50
2. Count Exact Text Match
=COUNTIF(B2:B100, "Completed")
Counts cells containing exactly "Completed" (case-insensitive)
3. Count Cells Containing Text
=COUNTIF(C2:C100, "*apple*")
Counts cells containing "apple" anywhere (matches "pineapple", "apple pie")
4. Count Blank Cells
=COUNTIF(D2:D100, "")
Counts empty cells in range
5. Count Non-Blank Cells
=COUNTIF(E2:E100, "<>")
Counts cells that are not empty
Dynamic Criteria (Cell Reference)
6. Count Greater Than Cell Value
=COUNTIF(A2:A100, ">"&F2)
Counts cells greater than value in F2 (concatenate operator with cell)
7. Count Matching Cell Text
=COUNTIF(B2:B100, G2)
Counts cells matching text in G2
Wildcard Examples
8. Count Starting With
=COUNTIF(A2:A100, "John*")
Counts "John", "Johnson", "John Smith"
9. Count Ending With
=COUNTIF(A2:A100, "*son")
Counts "Johnson", "Anderson", "Wilson"
10. Count With Pattern
=COUNTIF(A2:A100, "?????")
Counts cells with exactly 5 characters
Advanced Examples
11. Count Date Range (After Date)
=COUNTIF(A2:A100, ">"&DATE(2025,1,1))
12. Count NOT Equal
=COUNTIF(B2:B100, "<>Pending")
13. Count Between Values (Combine 2 COUNTIF)
=COUNTIF(A2:A100, ">=50") - COUNTIF(A2:A100, ">100")
Counts values between 50 and 100 (inclusive)
14. Count Errors
=COUNTIF(A2:A100, "#N/A")
15. Percentage Calculation
=COUNTIF(A2:A100, "Pass") / COUNTA(A2:A100) * 100
Calculate % of cells that say "Pass"
🎯 Pro Tips
- • Multiple criteria? Use COUNTIFS instead: =COUNTIFS(A:A, ">50", B:B, "Active")
- • Case-sensitive? Use SUMPRODUCT: =SUMPRODUCT((A2:A100="Apple")*1)
- • OR logic? Add multiple COUNTIF: =COUNTIF(A:A,"Red")+COUNTIF(A:A,"Blue")
- • Performance: Avoid entire column references (A:A) with large datasets
Frequently Asked Questions
Why is my COUNTIF not working?
Common issues: 1) Missing quotes around criteria (">50" not >50), 2) Extra spaces in cells (use TRIM), 3) Numbers stored as text, 4) Criteria syntax wrong (use "&" to combine: ">"&A1), 5) Range has errors that break count.
Can COUNTIF count multiple criteria?
No, COUNTIF handles one condition only. For multiple criteria use COUNTIFS: =COUNTIFS(A:A, ">50", B:B, "Active"). For OR logic, add COUNTIF functions: =COUNTIF(A:A,"Red")+COUNTIF(A:A,"Blue").
Is COUNTIF case-sensitive?
No, COUNTIF is case-insensitive. "Apple", "APPLE", and "apple" are all counted the same. For case-sensitive counting, use SUMPRODUCT: =SUMPRODUCT((EXACT(A2:A100,"Apple"))*1).
How do I count cells containing specific text?
Use wildcards with asterisk (*): =COUNTIF(A:A, "*apple*") counts any cell containing "apple" anywhere. For starting with: "apple*". For ending with: "*apple". Case-insensitive by default.
Can COUNTIF count dates?
Yes! Use date operators: =COUNTIF(A:A, ">"&DATE(2025,1,1)) counts dates after Jan 1, 2025. Or reference a cell: =COUNTIF(A:A, ">"&B1). For date range, use COUNTIFS with two date criteria.
What's faster: COUNTIF or SUMPRODUCT?
COUNTIF is much faster for simple counting. Use COUNTIF whenever possible. SUMPRODUCT is slower but more flexible for complex conditions. Example: COUNTIF for ">50", SUMPRODUCT for (A>50)*(B<100).