Excel COUNTA vs COUNT
COUNT counts numbers. COUNTA counts non-empty cells. Learn when to use each one, where they differ, and the mistakes that distort spreadsheet totals.
COUNT and COUNTA both count cells, but they answer different questions. COUNT counts numeric values. COUNTA counts cells that are not empty.
That difference matters in trackers, CRM lists, invoice logs, and budget reviews where a column may contain dates, text statuses, blanks, notes, or dollar amounts.
The syntax
=COUNT(value1, [value2], ...)=COUNTA(value1, [value2], ...)- value1 — the first range or value to count
- [value2] — optional additional ranges or values
Use COUNT when the question is "how many numbers are here?" Use COUNTA when the question is "how many cells have anything in them?"
Basic example
If A2:A20 contains invoice numbers, some numeric and some text-based:
=COUNTA(A2:A20)That counts every filled invoice number.
If D2:D20 contains invoice amounts:
=COUNT(D2:D20)That counts only rows with numeric amounts.
Step-by-step invoice log example
Say you have an invoice template log where column A is invoice ID, column B is client, column C is issue date, and column D is amount.
Step 1. Count invoices entered:
=COUNTA(A2:A200)Step 2. Count invoices that have valid numeric amounts:
=COUNT(D2:D200)Step 3. Compare the two results.
If COUNTA returns 87 and COUNT returns 84, three invoice rows probably have missing or non-numeric amounts.
That is a useful audit check because it tells you the log has records that may not be ready for reporting.
Where COUNT works better
Use COUNT for numeric fields:
- invoice amounts
- transaction amounts
- quantities
- rates
- dates stored as real Excel dates
- scores or percentages
Dates count as numbers in Excel because dates are stored as serial numbers. If a date column is properly formatted, COUNT can tell you how many rows have real date values.
Where COUNTA works better
Use COUNTA for fields that can contain text:
- client names
- invoice IDs
- task owners
- CRM stages
- categories
- notes
COUNTA is also useful in a budget tracker or expense tracker when you need to know how many rows have been filled in at all.
WATCH OUT
COUNTA counts formulas that return an empty string, such as ="", because the cell is not truly empty.
Common COUNT and COUNTA mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Using COUNT on a text ID column | Filled rows are missed | Use COUNTA |
| Using COUNTA on a formula column | Blank-looking formulas still count | Count a source column or use COUNTBLANK checks |
| Counting an entire column in a messy workbook | Old notes or stray values inflate the result | Limit the range to the table |
| Assuming dates are text | Real Excel dates count with COUNT | Check the cell format and underlying value |
Which one should you use?
If the column should contain numbers, dates, or amounts, start with COUNT.
If the column is a record identifier, status, name, owner, or text field, start with COUNTA.
For data quality checks, use both. COUNTA can count records entered, while COUNT can count records with valid numeric values.
Test the column before choosing the function
Take a small sample from the actual column and classify its values: numeric amounts, dates, text IDs, blank cells, and formulas that return "". COUNT is appropriate for numeric values and real Excel dates; COUNTA counts any value Excel considers present, including text and formulas that return an empty string. If the column mixes valid and invalid entries, report both counts so the user can see data quality rather than only a total.
Sources
- Microsoft Support: Use COUNTA to count cells that aren't blank
- Microsoft Support: Ways to count values in a worksheet
The Griddy way
Counting records is simple until the sheet mixes text IDs, blank-looking formulas, date serials, and missing amounts.
"Count how many invoices are entered, then show how many are missing valid dollar amounts"
Griddy can choose the right COUNT or COUNTA pattern for each column and add the audit checks directly to your spreadsheet.
Do this with AI
Turn the instructions into a finished spreadsheet.
Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.