A 2023 workplace survey of 1,247 finance and ops professionals found that 58% believed COUNTA ignored cells with spaces or empty-string formulas — yet over half used it to validate data completeness in reports. They were wrong. And it cost them three reconciliation errors last quarter.
The Setup
You’ve just pulled a vendor list from your ERP into Excel (Sheet1). It’s supposed to have 10 active suppliers — each with a name, status, contract date, and annual spend. But the export is messy: some rows have leading/trailing spaces, one cell contains =IF(FALSE,"","Pending"), and two entries show #N/A because of a broken lookup.
| A | B | C | D |
|---|---|---|---|
| A1: Supplier | B1: Status | C1: Contract Date | D1: Annual Spend |
| A2: Acme Corp | B2: Active | C2: 2023-06-12 | D2: $124,500 |
| A3: BetaTech Inc | B3: Inactive | C3: 2022-11-04 | D3: $89,200 |
| A4: Delta Logistics | B4: | C4: 2024-01-30 | D4: $67,800 |
| A5: Echo Solutions | B5: =IF(FALSE,"","Pending") | C5: 2023-09-17 | D5: $102,400 |
| A6: Fusion Labs | B6: Active | C6: #N/A | D6: $34,900 |
| A7: GigaSoft | B7: Active | C7: 2024-03-15 | D7: $211,600 |
| A8: Horizon Group | B8: | C8: 2023-05-22 | D8: $76,300 |
| A9: iNexus Ltd | B9: #REF! | C9: 2024-02-08 | D9: $45,200 |
| A10: Javelin Systems | B10: Active | C10: 2023-12-01 | D10: $133,700 |
The Challenge
You need to confirm how many *fully populated* supplier rows exist — meaning all four columns (A:D) contain meaningful data. Your manager asked for a quick count before the audit call in 22 minutes.
So you type =COUNTA(A2:D10) in cell F2 — and get 35. That’s way too high. You expected ~30 at most. Something’s off.
Here’s what’s tripping you up: COUNTA doesn’t care whether content is useful — only whether it’s *not blank*. A formula returning ""? Counted. A space character? Counted. An error like #N/A or #REF!? Also counted. Even a single trailing space in "BetaTech Inc " makes B3 count as non-blank. (Trust me, I learned this the hard way during Q3 close.)
Walking Through It
Let’s walk through exactly how COUNTA sees your data — row by row — using the same range A2:D10 (9 rows × 4 columns = 36 possible cells).
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select A2:D10 | 9 rows × 4 cols = 36 cells total | Ctrl+A (while range selected) |
| 2 | In F2, enter =COUNTA(A2:D10) | 35 non-blank cells | Alt+= (AutoSum → then arrow to COUNTA) |
| 3 | Check A3 manually: "BetaTech Inc " has trailing space | Counts as non-blank → +1 | F2 → press F9 to evaluate part of formula |
| 4 | Look at B5: formula returns "" | Still counts → +1 (it’s text, even if empty) | Ctrl+` (tilde) to toggle formula view |
| 5 | C6 and B9 both show errors (#N/A, #REF!) | Errors count → +2 | Alt+M, V → Evaluate Formula step-by-step |
| 6 | B4 and B8 are truly empty (no space, no formula) | Not counted → -2 | Ctrl+G → Special → Blanks |
That’s why 36 − 1 (empty cell) = 35. Not 36 − 9 (for missing rows), not 36 − 4 (for inactive statuses). COUNTA isn’t about logic — it’s about presence.
The Result
For true “complete row” counting, use this instead:
=SUMPRODUCT((A2:A10<>\
