COUNT Function: Complete Counting Guide (2025)
Quick Answer: COUNT counts numbers: =COUNT(A1:A10) counts numeric cells. COUNTA counts non-empty: =COUNTA(A1:A10) includes text. COUNTBLANK counts empty: =COUNTBLANK(A1:A10). COUNT ignores text/blanks, COUNTA counts everything except blanks.
What is COUNT?
COUNT is Excel's basic counting function. It counts how many cells in a range contain numbers. Simple, fast, and essential for data analysis.
COUNT
=COUNT(A1:A10)
Counts: Numbers only
Ignores text and blanks
COUNTA
=COUNTA(A1:A10)
Counts: Non-empty cells
Numbers, text, dates, errors
COUNTBLANK
=COUNTBLANK(A1:A10)
Counts: Empty cells
Truly blank cells only
Example Data:
| Cell | Value | Type |
|---|---|---|
| A1 | 100 | Number |
| A2 | Text | Text |
| A3 | (blank) | Empty |
| A4 | 50 | Number |
| A5 | TRUE | Boolean |
=COUNT(A1:A5) → 2 (counts A1, A4)
=COUNTA(A1:A5) → 4 (counts A1, A2, A4, A5)
=COUNTBLANK(A1:A5) → 1 (counts A3)
💡 Common Use Cases
- • Data validation: Count how many fields are filled
- • Survey responses: Count answered questions
- • Inventory tracking: Count items with quantity values
- • Attendance: Count present (non-blank) entries
- • Quality control: Count measurements or test results
COUNT Function Family
1. COUNT
=COUNT(value1, [value2], ...)
Counts cells containing numbers. Ignores text, blanks, logical values, and errors.
=COUNT(A1:A10) ← Count numbers in range=COUNT(A1:A5, C1:C5) ← Count multiple ranges=COUNT(10, 20, 30) ← Count direct valuesWhat it counts: Numbers (1, 2.5, -10), dates (stored as numbers)
What it ignores: Text, blanks, TRUE/FALSE, errors
2. COUNTA
=COUNTA(value1, [value2], ...)
Counts non-empty cells. Includes numbers, text, dates, errors, and logical values.
=COUNTA(A1:A10) ← Count non-empty cells=COUNTA(A:A) ← Count entire columnWhat it counts: Numbers, text, dates, TRUE/FALSE, errors (#N/A, #DIV/0!)
What it ignores: Empty cells only
3. COUNTBLANK
=COUNTBLANK(range)
Counts empty cells in a range.
=COUNTBLANK(A1:A10) ← Count blank cellsImportant: Cells with formulas returning "" (empty string) are NOT counted as blank
Alternative: =ROWS(A1:A10)-COUNTA(A1:A10)
Quick Comparison
| Function | Counts | Best For |
|---|---|---|
| COUNT | Numbers only | Numeric data, measurements |
| COUNTA | Non-empty cells | Form completion, any data |
| COUNTBLANK | Empty cells | Missing data, gaps |
10+ COUNT Examples
COUNT Examples
1. Basic Number Count
=COUNT(A2:A20)
Counts how many cells contain numbers
2. Count Multiple Ranges
=COUNT(A2:A10, C2:C10, E2:E10)
Count numbers across non-adjacent ranges
3. Count Entire Column
=COUNT(B:B)
Count all numbers in column B
4. Calculate Completion Percentage
=COUNT(A2:A20)/ROWS(A2:A20)*100&"%"
Percentage of cells with numeric data
COUNTA Examples
5. Count Non-Empty Cells
=COUNTA(A2:A20)
Counts cells with any data (text, numbers, dates)
6. Survey Response Rate
=COUNTA(B2:B100)/ROWS(B2:B100)
Calculate percentage of answered questions
7. Count Unique Values (with helper)
=SUMPRODUCT(1/COUNTIF(A2:A20, A2:A20))
Advanced: Count unique non-empty values
COUNTBLANK Examples
8. Count Missing Data
=COUNTBLANK(A2:A100)
Count how many cells are empty
9. Data Completeness Check
=IF(COUNTBLANK(A2:E2)>0, "Incomplete", "Complete")
Check if row has all required fields
10. Calculate Filled Percentage
=(ROWS(A:A)-COUNTBLANK(A:A))/ROWS(A:A)*100&"%"
Percentage of non-blank cells
Combined Techniques
11. Text vs Number Count
=COUNTA(A2:A10)-COUNT(A2:A10)
Counts text entries (non-numeric)
12. Attendance Tracker
=COUNTA(B2:Z2)&" of "&COLUMNS(B2:Z2)&" days"
Shows "15 of 25 days" format
🎯 Pro Tips
- • Empty strings: ="" is NOT blank for COUNTBLANK, but IS blank for COUNTA
- • Dates are numbers: COUNT includes dates (stored as numbers internally)
- • For conditions: Use COUNTIF for criteria-based counting
- • Entire columns: COUNT(A:A) is safe, doesn't slow down unless millions of rows
- • Array formulas: COUNT works with array results for advanced counting
Frequently Asked Questions
Why does COUNT ignore dates?
COUNT doesn't ignore dates! Dates in Excel are stored as numbers (days since Jan 1, 1900). COUNT includes dates. If your dates aren't counted, they're likely stored as text. Use DATEVALUE to convert text dates to real dates.
What's the difference between COUNT and COUNTA?
COUNT counts only numbers (including dates). COUNTA counts ALL non-empty cells (numbers, text, errors, TRUE/FALSE). Example: Range with 5, 'Text', blank → COUNT=1, COUNTA=2. Use COUNT for numeric analysis, COUNTA for data completeness.
Why doesn't COUNTBLANK count cells with formulas returning empty strings?
COUNTBLANK counts truly blank cells only. Formula =IF(A1='','','text') returns empty string '', which appears blank but isn't. Excel sees it as containing data. Use =COUNTIF(A:A,'') to count empty strings specifically.
How do I count cells with specific text?
Use COUNTIF, not COUNT. COUNT only counts numbers. =COUNTIF(A:A,'Apple') counts cells containing 'Apple'. =COUNTIF(A:A,'*apple*') counts cells containing 'apple' anywhere. COUNT can't handle criteria.
Can COUNT handle multiple criteria?
No, COUNT has no criteria parameter. Use COUNTIF for one condition: =COUNTIF(A:A,'>100'). Use COUNTIFS for multiple: =COUNTIFS(A:A,'>100',B:B,'Active'). COUNT is for simple numeric counting only.
Why is my COUNT result different from ROWS?
ROWS counts all cells (including blanks). COUNT counts only numbers. Example: 10-cell range with 3 numbers → ROWS=10, COUNT=3. To count all cells use: =ROWS(A1:A10)*COLUMNS(A1:A10).