Why does COUNT return 0 when your column clearly has numbers? Why does it ignore cells that look like numbers but contain leading apostrophes? Why does COUNTA give a different result than COUNT — even though both seem like 'counting' formulas?
The answer isn’t about syntax — it’s about what Excel *decides* counts as a number. And no, typing ‘123’ into a cell doesn’t guarantee Excel sees it as numeric. We’ll fix that — starting with real data you’d actually see on a procurement sheet.
The Setup
You manage vendor invoices for Alibaba Cloud’s AP team. Your raw data lives in Sheet1, columns A through D: Vendor name, Invoice ID, Amount, and Date received. You’ve pasted this from an email PDF — so some values are text, some are numbers, and one column has mixed formatting. Let’s look at rows 2–9 (A2:D9):
| Vendor | Invoice ID | Amount | Date |
|---|---|---|---|
| Nexus Logistics | INV-7821 | $12,450 | 2024-02-14 |
| BrightLink Tech | INV-7822 | '14,990 | 2024-02-15 |
| Acme Corp | INV-7823 | 16,200 | 2024-02-16 |
| Stellar Systems | INV-7824 | '11,875 | 2024-02-17 |
| Voyager Labs | INV-7825 | 13,400 | 2024-02-18 |
| TerraForm Inc | INV-7826 | '15,020 | 2024-02-19 |
| Orion Dynamics | INV-7827 | 10,550 | 2024-02-20 |
| Zenith Networks | INV-7828 | '12,760 | 2024-02-21 |
Notice the single quote before amounts in rows 3, 5, 7, and 9? That’s Excel’s way of forcing text entry — even if it looks like a number. Those cells live in C3, C5, C7, and C9. You’ll need to spot them before COUNT helps you.
The Challenge
Your manager asks: “How many valid invoice amounts do we have?” Not how many rows. Not how many non-blank cells. But how many *numeric* amounts — because only those feed into your monthly accrual model. You type =COUNT(C2:C9) in cell F2… and get 3. But you see five entries that look like numbers. What happened?
COUNT only counts cells containing numbers — not text that looks like numbers, not dates stored as text, not numbers with currency symbols unless they’re formatted properly. And here’s the kicker: Excel treats ‘$12,450’ in C2 as text — because of the dollar sign and comma — unless it’s been formatted as Currency or Accounting. So even C2 won’t count. (Trust me, I learned this the hard way during Q3 close.)
Walking Through It
We’ll fix this in three stages — and show exactly what changes at each step.
Step 1: See what COUNT really sees
Enter =COUNT(C2:C9) in F2. Result: 2. Only C4 (16,200) and C6 (13,400) and C8 (10,550) are true numbers. Wait — that’s three. Why does it say 2? Because C8 is actually formatted as text too — you just can’t tell by looking. Select C8, press Ctrl+1, and check the Number tab: it says ‘Text’. So only C4 and C6 qualify. That’s why COUNT returns 2.
Step 2: Clean the column
Select C2:C9 → press Alt+H+F+A (Home → Format → Clear Formats). Now all cells are General. Then select C2:C9 again → go to Data tab → Text to Columns → Delimited → Next → Next → Finish. This strips hidden characters and forces re-evaluation. Now reapply Accounting format to C2:C9.
Step 3: Verify numeric status
In D11, enter =ISNUMBER(C2) and drag down to D19. You’ll see TRUE only for cells Excel now treats as numbers. Then try =COUNT(C2:C9) again — now it returns 5.
| Before COUNT | After Cleaning |
|---|---|
| C2: $12,450 (text) | C2: 12450 (number) |
| C3: '14,990 (text) | C3: 14990 (number) |
| C4: 16,200 (number) | C4: 16200 (number) |
| C5: '11,875 (text) | C5: 11875 (number) |
| C6: 13,400 (number) | C6: 13400 (number) |
Pro tip: Try =COUNT(C2:C9)+COUNTA(C2:C9)-COUNT(C2:C9)? No — that’s nonsense. But here’s what *is* useful: =SUMPRODUCT(--ISNUMBER(C2:C9)). It counts numeric cells *without* needing to clean first. Try it in G2 — it returns 2 before cleaning, 5 after. That’s your verification lifeline.
The Result
Here’s your final cleaned dataset (C2:C9), with COUNT now working correctly:
| Vendor | Amount (cleaned) | COUNT result |
|---|---|---|
| Nexus Logistics | 12450 | =COUNT($C$2:$C$9) → 5 |
| BrightLink Tech | 14990 | |
| Acme Corp | 16200 | |
| Stellar Systems | 11875 | |
| Voyager Labs | 13400 | |
| TerraForm Inc | 15020 | |
| Orion Dynamics | 10550 | |
| Zenith Networks | 12760 |
What Could Go Wrong
Mistake #1: Using COUNT on a range that includes headers
You put =COUNT(C1:C9) instead of =COUNT(C2:C9). Since C1 says “Amount”, COUNT ignores it — but it still wastes CPU cycles scanning a non-numeric cell. Not dangerous, but sloppy. Always start below headers.
Mistake #2: Assuming COUNT ignores blanks — it does, but not empty strings
If C7 contains ="" (a formula returning blank), COUNT skips it. But if it contains a space character (ASCII 32), COUNT ignores it too — because spaces aren’t numbers. Use =LEN(C7) to check.
Mistake #3: Confusing COUNT with COUNTIF when filtering
You apply an AutoFilter to hide rows, then run COUNT. It counts *all* cells in the range — visible or not. To count only visible numbers, use =SUBTOTAL(102,C2:C9). That 102 is key: it tells SUBTOTAL to use COUNT on visible cells only.
Next step: Open your invoice sheet right now. In an empty cell, type =SUMPRODUCT(--ISNUMBER(C2:C100)). Compare that to =COUNT(C2:C100). If they differ, you’ve got text masquerading as numbers — and now you know how to find and fix them.
