Why does =SUM(B2:B20) return 0 when your numbers are clearly there? Why does it include a cell with 'N/A' but ignore one with '5.0'? Why does copying the same formula to column C give a different result than column B?
Quick Answer
SUM adds only numeric values — nothing else. It ignores empty cells, logical values (TRUE/FALSE), text (even if it looks like "123"), and errors — unless those errors are inside the range itself, in which case the whole formula returns #VALUE! or #N/A. Hidden rows? SUM includes them. Filtered-out rows? Still included. That’s the core behavior — and it trips up nearly everyone at least once.
All the Methods
| Method | Steps | Best For | Limitations | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|---|---|---|
| =SUM(range) | Type =SUM(A1:A100) → Enter | Simple totals across contiguous numbers | Ignores text, TRUE/FALSE, errors in range — but fails entirely if any cell contains #N/A | 0.02 sec | High (if no errors) | Easy |
| =SUMPRODUCT(--ISNUMBER(A1:A100)*A1:A100) | Enter as regular formula (no Ctrl+Shift+Enter) | Ranges with mixed content (text labels, blanks, numbers as text) | Slower; harder to audit; doesn’t handle #N/A gracefully without IFERROR | 0.18 sec | Very High | Medium |
| AutoSum (Alt+=) | Select cell below or right of numbers → Alt+= | Fast entry when data is clean and adjacent | Often guesses wrong direction; skips non-contiguous blocks; ignores manual formatting | 0.01 sec (plus human decision time) | Medium (depends on layout) | Easy |
| SUBTOTAL(109, range) | =SUBTOTAL(109,B2:B100) → works with filters | Filtered lists or collapsed outlines | Doesn’t ignore hidden rows unless filtered — manual hiding still counts | 0.03 sec | High (for visible data only) | Medium |
| AGGREGATE(9,6,range) | =AGGREGATE(9,6,A1:A100) — 9=sum, 6=ignore errors | Ranged data with #N/A, #DIV/0!, or other errors | No built-in help tooltip; syntax feels cryptic at first | 0.04 sec | Very High | Medium-Hard |
Method 1 Deep Dive
Let’s walk through =SUM(B2:B12) using real sales data from Q1:
| Rep | Jan Sales | Feb Sales | Mar Sales |
|---|---|---|---|
| Sarah Chen | $12,450 | $14,200 | $13,890 |
| Diego Mora | $9,750 | #N/A | $11,320 |
| Priya Patel | "$8,600" | $10,150 | $9,440 |
| James Wu | $15,200 | $16,050 | (blank) |
If you enter =SUM(B2:B5) — expecting Jan totals — you’ll get #N/A. Not zero. Not an error message telling you *where* the problem is. Just #N/A. That’s because SUM can’t tolerate any error value in its range. Even one #N/A kills the whole calculation. (Trust me, I learned this the hard way during a live board demo.)
Also notice Priya’s Jan value: "$8,600". The quotes make it text — not a number. SUM ignores it silently. So =SUM(B2:B5) returns $37,400 — missing Priya’s $8,600 entirely. You won’t see a warning. No red triangle. Just quiet omission.
Method 2 Deep Dive
Now try =AGGREGATE(9,6,B2:B5). Same range. Same data. This time, you get $46,000.
Here’s why: 9 means “SUM”, and 6 tells Excel to “ignore error values”. So it skips Diego’s #N/A and keeps going. It still ignores Priya’s quoted "$8,600" — because AGGREGATE also only sums numbers — but at least it doesn’t crash.
You can combine options: =AGGREGATE(9,7,B2:B5) ignores errors and hidden rows. =AGGREGATE(9,5,B2:B5) ignores errors and nested SUBTOTAL/AGGREGATE functions. These flags (the second argument) are rarely taught — but they’re baked into Excel since 2010.
Pro tip: If you’re auditing someone else’s sheet and see a SUM returning zero unexpectedly, don’t just check for blanks. Press Ctrl+` (grave accent) to toggle formula view. Look for invisible characters — like a space before a number (" 123") or trailing apostrophes ("'123"). Both turn numbers into text. SUM won’t complain. It’ll just vanish them.
Cheat Sheet
| Task | Formula | Shortcut | Notes |
|---|---|---|---|
| Basic sum of A1:A10 | =SUM(A1:A10) |
Alt += (then arrow + Enter) | Fails on any #N/A or #VALUE! in range |
| Sum ignoring errors | =AGGREGATE(9,6,A1:A10) |
None (type manually) | Flag 6 = ignore errors; 9 = SUM |
| Sum only visible rows (after filter) | =SUBTOTAL(109,A1:A10) |
Alt+= then press Backspace, type SUBTOTAL | 109 = SUM + ignore hidden rows |
| Sum numbers disguised as text | =SUMPRODUCT(--ISNUMBER(--A1:A10)*--A1:A10) |
None — use Paste Special → Add 0 first | Converts "123" → 123 before summing |
| Check for text-numbers | =ISTEXT(A1) or =ISNUMBER(A1) |
F2 → F9 to evaluate part of formula | Test individual cells before summing ranges |
