Why SUM breaks when a cell errors, but COUNT keeps working
One error cell anywhere in a SUM range poisons the whole result. The exact same range handed to COUNT comes back with a clean number, as if the error cell simply wasn't there.
of real formula-bearing workbooks in a 4,000-file Enron sample already contain at least one cached error cell (FUSE: 14.3%). Of those, 71–83% also use SUM somewhere in the sheet — meaning "a SUM formula reaching across a pre-existing error" is a routine shape in real models, not an edge case.
The answer
SUM, AVERAGE, MAX and MIN all treat an error value as contagious: if any cell in the range they're reducing holds #DIV/0!, #REF!, #N/A or any other error, the function returns that same error instead of a number — even if the erroring cell is irrelevant to the sum you actually wanted.
COUNT and COUNTA do not. COUNT walks the range and tallies only the cells holding an actual number, treating errors, text and blanks alike as "not a number, skip it." COUNTA is the counterpart for "not blank" — it counts the error cell and the text cell too, just not as numeric contributions to anything.
Reproduce it
Column A: 10, 20, the text "text", a real #DIV/0! error, 30. Six formulas over it, evaluated by LibreOffice:
| Formula | Result |
|---|---|
SUM(A1:A5) | #DIV/0! |
COUNT(A1:A5) | 3 |
COUNTA(A1:A5) | 5 |
AVERAGE(A1:A5) | #DIV/0! |
MAX(A1:A5) | #DIV/0! |
SUM(A1:A3,A5) (no error in range) | 60 |
The last row is the other half of the rule: SUM(A1:A3,A5) — 10 + 20 + "text" + 30 — comes back 60, not an error and not 70. The text cell is silently skipped, not coerced to zero via any visible mechanism and not flagged; it simply isn't a number, so SUM doesn't count it, the same way COUNT doesn't count the error cell.
Excel vs. most engines
Excel (and LibreOffice, matching it)
Draws a hard line between "reducing to a value" (SUM/AVERAGE/MAX/MIN — errors propagate) and "counting what's there" (COUNT/COUNTA — errors are just another kind of non-numeric, ignorable cell).
A simpler, wrong rule
A formula engine that applies one blanket rule — "any error anywhere in an argument propagates" — uniformly across every aggregate function is easier to implement, and silently changes COUNT's answer on any range that also happens to contain an old, unrelated error cell.
This exact distinction is one of the rules Basis's engine had to get right to reach 0 mismatches across 91,022 real formulas on the Royal Properties underwriting model (153k cells) differentially verified against Excel's own cached values — getting it wrong doesn't fail a style guide, it fails a real file's own cached numbers.
Model has a broken reference somewhere? Basis's audit maps every dependent that silently inherited an error, not just the cell where it originated.