The first thing most people do when they need to count cells is type =COUNT(A1:A10) and assume it counts everything non-empty. That’s almost always wrong — especially if your range includes dates stored as text, logical values like TRUE/FALSE, or numbers formatted as currency with hidden decimals. COUNT() only sees numbers. Not dates. Not Booleans. Not numbers typed as text. And it skips errors entirely — silently.
Quick Answer
COUNT() counts only cells containing numbers, dates, or formulas that return numbers — nothing else. It ignores text, logical values (TRUE/FALSE), errors (#N/A, #VALUE!), empty strings (""), and numbers stored as text. Use COUNTA() for non-blank cells, COUNTBLANK() for truly empty cells, and COUNTIF()/COUNTIFS() when you need conditions.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| COUNT(range) | Type =COUNT(B2:B12) and press Enter |
Tallying numeric entries only — e.g., sales figures, IDs, dates | Ignores text numbers ("123"), TRUE/FALSE, errors, and empty strings |
| COUNTA(range) | Type =COUNTA(C2:C12) |
Counting all non-blank cells — including "0", "FALSE", "#N/A", and "" | Counts error cells and zero-length strings — can inflate totals |
| COUNTBLANK(range) | Type =COUNTBLANK(D2:D12) |
Finding truly blank cells — no formula, no space, no "" | Counts cells with ="" but not cells with spaces or non-breaking spaces |
| COUNTIF(range, criteria) | Type =COUNTIF(E2:E12,">1000") |
Conditional counting — e.g., orders over $1,000 or status = "Shipped" | Single condition only; use COUNTIFS() for multiple criteria |
| COUNTIFS(range1, crit1, range2, crit2) | Type =COUNTIFS(F2:F12,"Shipped",G2:G12,">=2024-03-01") |
Multi-criteria counting — e.g., shipped orders after March 1, 2024 | Each range must be same size; case-insensitive matching only |
Method 1 Deep Dive
Let’s test COUNT() on real data in A1:G12:
| Name | Sales | Status | Date | Qty | Region | Notes |
|---|---|---|---|---|---|---|
| Sarah Chen | $45,200 | Shipped | 2024-03-15 | 12 | APAC | |
| Diego Mendoza | $12,800 | Pending | 2024-03-18 | 5 | EMEA | #N/A |
| Priya Kapoor | "$33,100" | Shipped | 2024-03-22 | 8 | APAC | TRUE |
| James Wilson | $0 | Cancelled | 2024-03-10 | 0 | AMER | "" |
| Lina Zhang | 18,500 | Shipped | 2024-04-01 | 15 | APAC | FALSE |
In B2:B6, we have five entries. But only three are true numbers: $45,200 (B2), $12,800 (B3), and 18,500 (B5). "$33,100" in B4 is text — Excel stores it as a string because of the quotes. $0 in B6 is counted. So =COUNT(B2:B6) returns 4, not 5. Try it yourself: select B7, type =COUNT(B2:B6), then press Ctrl+Enter.
Here’s the counterintuitive part: Dates *are* numbers internally. So =COUNT(D2:D6) returns 5 — all five date entries count, even though they display as “2024-03-15”. Excel stores them as serial numbers (e.g., 45366). That’s why they’re counted.
Method 2 Deep Dive
COUNTIFS() is where most people stall — usually because they misalign ranges. Let’s count shipped orders from APAC after March 15, 2024.
Use this exact formula in H2:=COUNTIFS(C2:C12,"Shipped",F2:F12,"APAC",D2:D12,">=2024-03-16")
Note: D2:D12 must be formatted as Date — not Text. If your dates are text (check with =ISTEXT(D2)), COUNTIFS() won’t match them. Also, never use >2024-03-15 — Excel treats that as subtraction (2024 minus 3 minus 15 = 2006). Always wrap dates in quotes with >= or use cell references: >=H1 where H1 contains 2024-03-16.
Keyboard shortcut: To quickly insert COUNTIFS(), press Alt + M + U + S (opens Function Arguments dialog for COUNTIFS).
Try it on rows 2–12 above. With our sample, only Sarah Chen (D2 = 2024-03-15 → doesn’t meet >=2024-03-16) and Lina Zhang (D5 = 2024-04-01 → qualifies) match all three conditions. So result = 1.
Cheat Sheet
| Function | Syntax | Shortcut (Alt key) | Key Tip |
|---|---|---|---|
| COUNT | =COUNT(A1:A10) |
Alt+M, U, N | Dates count. "123" does not. TRUE/FALSE do not. |
| COUNTA | =COUNTA(A1:A10) |
Alt+M, U, A | Counts "" and #N/A — watch for false positives. |
| COUNTBLANK | =COUNTBLANK(A1:A10) |
Alt+M, U, B | Does NOT count cells with =TRIM("") or non-breaking spaces. |
| COUNTIF | =COUNTIF(A1:A10,">500") |
Alt+M, U, I | Criteria with operators must be in quotes: ">100", "<>""". |
| COUNTIFS | =COUNTIFS(A1:A10,"X",B1:B10,">100") |
Alt+M, U, S | All ranges must be same size — mismatch = #VALUE!. |
