It’s 3:12 PM. You just pasted 87 rows of sales leads from a CRM export into Sheet1. Column D has phone numbers, but some are blank, some say 'N/A', and three contain text like 'Call later'. You type =COUNT(D2:D88) and get 62. Your boss asks, 'So how many actual numbers did you get?' You hesitate. You don’t know.
Quick Answer
The COUNT function in Excel counts only cells that contain numbers — including dates, times, percentages, and negative values — but excludes text, logical values (TRUE/FALSE), errors, and empty cells. It ignores numbers stored as text, even if they look identical to real numbers (e.g., '123' vs. 123).
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| COUNT | =COUNT(A1:A100) | Counting numeric entries only | Ignores numbers formatted as text; no wildcard or condition support |
| COUNTA | =COUNTA(B1:B100) | Counting non-blank cells of any type | Counts '0', 'FALSE', and even a single space — not just numbers |
| COUNTIF | =COUNTIF(C1:C100,">0") | Counting numbers meeting one condition | Can’t handle multiple criteria without COUNTIFS |
| COUNTIFS | =COUNTIFS(D1:D100,">=100",D1:D100,"<500") | Counting numbers across multiple conditions | Slower on large ranges (>100k rows); case-insensitive only |
| SUMPRODUCT + ISNUMBER | =SUMPRODUCT(--ISNUMBER(E1:E100)) | Counting true numbers — including those hidden by formatting | More complex; slower than COUNT on huge datasets |
Method 1 Deep Dive
Let’s test COUNT on real data. Open a new sheet. Paste this into A1:E10:
| Name | Sales | Date Closed | Status | Notes |
|---|---|---|---|---|
| Sarah Chen | 24500 | 2024-03-15 | Won | - |
| James Okafor | 31200 | 2024-03-18 | Won | Follow up Q2 |
| Lena Park | "18900" | 2024-03-22 | Lost | Price too high |
| Diego Mendoza | #N/A | 2024-03-25 | Pending | Awaiting approval |
| Anya Patel | 0 | 2024-03-27 | Won | - |
| Rajiv Singh | " " | 2024-03-29 | Lost | No response |
| Maya Torres | -7500 | 2024-04-01 | Refund | Cancelled order |
| Tariq Ali | 22.5% | 2024-04-03 | Won | Discount applied |
| Yuki Tanaka | TRUE | 2024-04-05 | Pending | Legal review |
| Elena Dubois | 2024-04-07 | Won | - |
Now enter =COUNT(B2:B11) in cell G2. Result: 6.
Why not 10? Let’s check each value in B2:B11:
- B2: 24500 → number → counted ✅
- B3: 31200 → number → counted ✅
- B4: "18900" → text (quoted) → ignored ❌
- B5: #N/A → error → ignored ❌
- B6: 0 → number → counted ✅
- B7: " " → space inside quotes → text → ignored ❌
- B8: -7500 → negative number → counted ✅
- B9: 22.5% → percentage → stored as decimal (0.225) → counted ✅
- B10: TRUE → logical → ignored ❌
- B11: blank → ignored ❌
That’s 6. This is the core behavior: COUNT only sees what Excel internally treats as numeric data types — nothing else.
Surprising tip: Dates and times count — because Excel stores them as serial numbers (e.g., 2024-03-15 = 45366). So =COUNT(C2:C11) returns 9. Even though column C looks like dates, Excel sees numbers.
Method 2 Deep Dive
Now try =COUNTA(B2:B11) in G3. Result: 9.
COUNTA counts everything except truly empty cells. That includes:
- B4: "18900" (text)
- B5: #N/A (error)
- B7: " " (space — not empty)
- B10: TRUE (logical)
- B2–B9, B11: all non-empty
Only B11 is blank — so 10 − 1 = 9.
But here’s where it gets dangerous. Say your finance team sends a report where negative numbers are entered as text: "(1,250)" instead of -1250. COUNT won’t see them. COUNTA will — but it’ll also count headers, footers, and “N/A” labels.
Do this now: In cell H2, enter =ISNUMBER(B4). It returns FALSE. That’s your diagnostic tool. Drag it down to H11. Only cells returning TRUE are counted by COUNT.
Need to fix text-numbers? Select B4:B6, go to Data tab → Text to Columns → Finish. Or use =VALUE(B4) in a helper column — but only if you’re sure the text is clean.
Keyboard shortcut: To quickly select a full column of data (even with blanks), click any cell in the column (e.g., B5), then press Ctrl+Shift+Down Arrow. That selects from B5 down to the last non-blank cell. Then press Alt+H+F+J to open Find & Replace — useful for spotting " characters before cleaning.
Cheat Sheet
| Task | Formula | Shortcut / Tip | Cell Example |
|---|---|---|---|
| Count only numbers | =COUNT(A1:A100) | Use Ctrl+Shift+Down to select range first | G2 |
| Count non-blanks (any type) | =COUNTA(B1:B100) | Counts spaces — trim with TRIM() if needed | G3 |
| Count numbers > 1000 | =COUNTIF(C1:C100,">1000") | Quotes required around criteria | G4 |
| Count numbers between 500–2000 | =COUNTIFS(D1:D100,">=500",D1:D100,"<=2000") | Each condition needs its own range | G5 |
| Count true numbers only (ignore text-numbers) | =SUMPRODUCT(--ISNUMBER(E1:E100)) | Double minus converts TRUE/FALSE to 1/0 | G6 |
| Diagnose why a cell isn’t counted | =ISNUMBER(F1) | Drag down to scan entire column | H2:H11 |
| Convert text-numbers to real numbers | Paste 1 → Select range → Alt+E+S+V → Enter | Multiply by 1 via Paste Special | — |
