Sum, average, or find MAX while ignoring formula errors (#DIV/0!, #N/A)

Why one #DIV/0! breaks an entire SUM or AVERAGE

Standard SUM, AVERAGE, MIN, and MAX propagate the first error they encounter in a range—so a single unpopulated row dividing by zero (#DIV/0!) or a missing lookup (#N/A) turns your grand total into an error.

ISNUMBER(A2:A6) returns TRUE strictly for valid numeric values and FALSE for every error type (#DIV/0!, #N/A, #VALUE!), blanks, and text. Passing FILTER(A2:A6, ISNUMBER(A2:A6), 0) into SUM or MAX aggregates clean numbers safely.

Error-proof SUM, AVERAGE, and MAX formulas

Include 0 as FILTER's 3rd if_empty argument inside SUM so an all-error column returns 0 instead of #CALC!.

=SUM(FILTER(A2:A6, ISNUMBER(A2:A6), 0))

Error-proof AVERAGE formula

Only numeric observations form the denominator. An entirely nonnumeric/error range has no defined mean, so return a message rather than a misleading zero.

=IF(COUNT(A2:A6)=0, "No numeric data", AVERAGE(FILTER(A2:A6, ISNUMBER(A2:A6))))

Inputs and interpretation

The SUM fallback of zero is a chosen empty-total convention; MAX with a zero fallback likewise needs a clearly stated no-observation policy. A zero fallback is not added to a nonempty filtered range. Skipping errors can hide incomplete data: report or investigate excluded rows rather than silently treating the summary as complete.

Further reading

• Microsoft Support — IS functions