Most Excel trainers tell you COUNTA counts 'non-empty cells.' That’s dangerously incomplete. It counts cells with any content — including #N/A errors, zero-length strings (""), and spaces typed with the spacebar. If you’re using COUNTA to check for user input, you’ll get false positives. Every time.
Quick Answer
COUNTA counts cells that contain anything: numbers, text, logical values (TRUE/FALSE), errors (#REF!, #VALUE!), and even zero-length strings (""). It ignores truly blank cells — but only those with no formula and no characters at all. A cell with ="" or a single space counts as non-blank.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| =COUNTA(A1:A12) | Type formula directly or use Formulas → More Functions → Statistical → COUNTA | Quick headcount of entries in a clean column | Fails if range contains "" from formulas or accidental spaces |
| =SUMPRODUCT(--(TRIM(A1:A12)<>"")) | Enter as regular formula (no Ctrl+Shift+Enter needed) | Counting cells with *visible* content — ignores spaces and "" | Slower on >50k rows; won’t catch leading/trailing spaces inside TRIM unless used correctly |
| =COUNTIF(A1:A12,"?*") + COUNTIF(A1:A12,"*?") | Two COUNTIFs: one for text starting with any char, one ending with any char | Counting cells with actual text (excludes numbers, errors, blanks) | Excludes numeric entries like 42 or 3.14 — even if they’re meaningful data |
| Alt + M, M, S (Formula Auditing → Evaluate Formula) | Select cell with COUNTA → Alt+M,M,S → step through evaluation | Diagnosing why COUNTA returns unexpected results | Only works on one cell at a time; requires manual inspection |
| Conditional Formatting + COUNTA | Highlight cells with =LEN(TRIM(A1))=0 → then COUNTA on visible range | Visual audit of 'empty-looking' but non-blank cells | Doesn’t change COUNTA result — just reveals root cause |
Method 1 Deep Dive
Let’s test =COUNTA(B2:B11) on real payroll data:
| Employee | Status | Start Date |
|---|---|---|
| Sarah Chen | Active | 2023-04-12 |
| James Rios | On Leave | 2023-09-05 |
| Maya Patel | #N/A | 2024-01-22 |
| Diego Torres | "" | 2024-03-15 |
| Aisha Kim | 2023-11-30 | |
| Rajiv Singh | Terminated | 2022-07-18 |
| Lena Wu | Active | 2024-02-09 |
| Tariq Ali | #REF! | 2023-08-01 |
| Zara Lin | " " | 2024-04-03 |
| Omar Hassan | [blank] | 2023-05-11 |
In B2:B11 (Status column), COUNTA returns 9 — not 7. Why? Because rows 4 (""), 5 (space), 8 (#REF!), and 9 (" ") all count. Only row 10 is truly blank. Use =LEN(TRIM(B4)) to confirm B4 is 0, yet COUNTA sees it. This is why relying on COUNTA for data validation fails.
Method 2 Deep Dive
The reliable alternative: =SUMPRODUCT(--(TRIM(B2:B11)<>\
