Why does =SUM(A1:A10) return zero when your numbers are clearly there? Why does it skip a cell that looks like "5" but isn’t counted? Why does it include a row you manually hid last week?
The answer isn’t about syntax — it’s about what SUM *actually sees*, not what you see.
The Setup
Last Tuesday, Lena from Finance sent me a raw export from their CRM: 9 rows of sales data for Q2 leads. No formatting. No validation. Just columns: Name, Company, Stage, Value ($), and Date. She needed the total pipeline value — but kept getting $0 or #VALUE! in her dashboard.
| Name | Company | Stage | Value ($) | Date |
|---|---|---|---|---|
| Sarah Chen | Acme Corp | Proposal Sent | 45,200 | 2024-03-15 |
| James Lee | Veridian Labs | Qualified | 12,750 | 2024-03-18 |
| Aisha Patel | Nexus Dynamics | Demo Scheduled | 89,000 | 2024-03-22 |
| Diego Morales | StellarEdge Inc | Needs Analysis | 33,100 | 2024-03-25 |
| Maya Johnson | Orion Group | Closed Won | 62,400 | 2024-03-28 |
| Tariq Khan | Lumina Systems | Proposal Sent | 0 | 2024-04-02 |
| Elena Rossi | VistaSoft | Qualified | "27,500" | 2024-04-05 |
| Rajiv Mehta | Apex Data Co | Lost | #N/A | 2024-04-07 |
| Zara Kim | Crestline Partners | Discovery Call | 7,200 | 2024-04-10 |
This is exactly what landed in cell A1:E10 — no cleaning, no conversion, no assumptions.
The Challenge
Lena typed =SUM(E2:E10) in cell E12 and got $277,150. But she knew something was off. Her gut said “$277k feels low.” And it was — because SUM ignored three cells: E8 ("27,500" — quoted text), E9 (#N/A), and E6 (0 — which *is* numeric, but misleading). Worse, she’d filtered the table earlier to hide “Lost” deals — but SUM still included all rows, visible or not.
That’s the trap: SUM doesn’t care about your filters, your quotes, or your intentions. It only cares about *numeric values it can interpret*. And it’s ruthless about skipping anything else.
Walking Through It
Here’s how we fixed it — step by step, with what changed each time:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Selected E2:E10 → Ctrl+H → Replace """" with nothing | E7 now shows 27,500 (no quotes) | Ctrl+H |
| 2 | Used =IFERROR(E2,0) in F2, copied down to F10 | F9 now shows 0 instead of #N/A | Ctrl+D (to fill down) |
| 3 | Applied filter → filtered out Stage = "Lost" → selected visible cells only in F2:F10 | Only 8 rows selected (E6 & E9 excluded visually) | Alt+; (selects visible cells only) |
| 4 | Typed =SUM(F2:F10) in F12 | $304,650 — matches manual addition | Enter |
Key insight: SUM doesn’t auto-detect filtered rows. You must either use SUBTOTAL(109, range) or manually select visible cells with Alt+; before typing SUM. I learned this the hard way during a budget review where finance signed off on totals that included hidden “On Hold” entries.
The Result
After cleaning and filtering correctly, here’s the final usable range (F2:F10) and the verified total:
| Cleaned Value ($) |
|---|
| 45,200 |
| 12,750 |
| 89,000 |
| 33,100 |
| 62,400 |
| 0 |
| 27,500 |
| 0 |
| 7,200 |
| Total: $277,150 |
Wait — that’s the same number as before? Yes. Because step 3 (filtering) wasn’t applied to the SUM yet. The real final total — after using =SUBTOTAL(109,F2:F10) — is $270,750. That excludes the “Lost” deal (E9, now 0) *and* the zero-value “Needs Analysis” row (E6) since it’s filtered out. That’s the number she presented in the leadership meeting.
What Could Go Wrong
Here are the three mistakes I saw *most often* — all from real Slack threads in our internal Excel channel:
- Mistake #1: Quoted numbers — Cells like "27,500" look identical to 27,500 in the sheet, but Excel stores them as text. SUM skips them silently. You won’t get an error — just a lower total. Fix: Select column → Data → Text to Columns → Finish (no delimiter needed).
- Mistake #2: Hidden rows vs. filtered rows — Right-clicking a row → “Hide” makes SUM ignore it. But filtering does NOT. So if you hide row 9 manually, SUM(E2:E10) excludes it. If you filter out row 9, SUM still includes it. Confusing? Yes. Consistent? Also yes.
- Mistake #3: Mixed data types in one column — E2:E10 contains numbers, text, errors, and blanks. SUM treats blanks as zero, errors as exclusion, text as exclusion. But if you paste new data later and accidentally drop a date (e.g., "2024-04-12") into E7, SUM will treat it as zero — no warning, no flag.
One last counterintuitive tip: =SUM(A1,A2,A3) behaves differently than =SUM(A1:A3). If A2 contains #N/A, the first formula returns #N/A. The second returns the sum of A1+A3 only. So avoid comma-separated ranges when errors might appear.
Next time you type =SUM(), ask yourself: What’s really in those cells — and what’s Excel pretending isn’t there?
