Quick Navigation
COUNTIF Function
Summary
The COUNTIF function is a powerful statistical tool that counts the number of cells within a specified range that meet a single given criterion. Perfect for data analysis tasks like tracking occurrences, filtering conditions, and generating summary statistics from large datasets.
Syntax
COUNTIF(range, criteria)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| range | Range |
Yes | Cells to count. Supports contiguous or non-contiguous ranges, named ranges, and arrays. |
| criteria | Criteria |
Yes | Matching condition using numbers (32), comparisons (">32"), cell refs (B4), text ("apples"), or wildcards. |
Using the COUNTIF Function
COUNTIF excels at single-condition counting across datasets. Use it for inventory tracking, sales analysis, attendance monitoring, or any scenario requiring frequency counts based on one specific condition. Combine multiple COUNTIF functions for complex analysis or upgrade to COUNTIFS for multi-criteria needs.
Common COUNTIF Examples
Count Specific Text
=COUNTIF(A2:A10,"apples")Returns 3 - counts cells containing "apples" in range A2:A10.
Count Using Cell Reference
=COUNTIF(A2:A10,A2)Returns 2 - counts occurrences of value in A2 within the range.
Count Numbers Greater Than Value
=COUNTIF(B2:B10,">55")Returns 4 - counts cells in B2:B10 with values above 55.
Count Non-Blank Text Cells
=COUNTIF(A2:A10,"*")Returns 7 - counts all non-empty text cells using wildcard.
Count Exact Length Text
=COUNTIF(A2:A10,"?????")Returns 2 - counts 5-character text entries using ? wildcards.
Exclude Specific Value
=COUNTIF(B2:B10,"<>"&B5)Returns 8 - excludes value in B5 from count using <> operator.
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error with external workbook
Cause: Formula references closed workbook with calculated cells
Solution: Open the referenced workbook or use local data
Incorrect count for long text (>255 chars)
Cause: COUNTIF limitation with very long strings
Solution: Use CONCATENATE or & to build criteria: "part1"&"part2"
No matches found (returns 0)
Cause: Missing quotes around text criteria or leading/trailing spaces
Solution: Enclose text in quotes and use TRIM/CLEAN on source data
Notes
- Case-insensitive text matching
- Wildcards: * = any characters, ? = single character, ~ = literal wildcard
- Supports 3D references and dynamic arrays in Excel 365
- Does NOT count by cell color or formatting (use VBA for that)
- Maximum range size limited by Excel's 1,048,576 row limit
Compatibility
Available in: Excel 2000, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2000+, Excel 2007+, Excel 2010+, Excel 2013+, Excel 2016+, Excel 2019+, Excel 365+