How to sum a range and ignore errors in Excel

Why SUM returns #N/A

The sheet is a month of sales for a kitchen shop. Column E looks up each product's unit cost in the cost table in J:K, F works out the profit and G the profit per unit. Two things went wrong, as they do in real files. The new spice rack (KT-107) is not in the cost table yet, so its VLOOKUP returns #N/A, and that #N/A flows into its profit and profit per unit. The tea towels sold nothing, so 0 divided by 0 units gives #DIV/0! in G5.

SUM, AVERAGE, MAX and MIN all return the first error they meet. One missing cost is enough to wipe out the whole total.

AGGREGATE: sum and skip every error

AGGREGATE (Excel 2010 and later) runs one of 19 calculations and lets you choose what to leave out. The first argument picks the calculation: 1 AVERAGE, 2 COUNT, 4 MAX, 5 MIN, 9 SUM, 12 MEDIAN, 14 LARGE, 15 SMALL. The second picks the option: 6 ignores error values, 7 ignores errors and hidden rows, and 2 ignores errors plus any SUBTOTAL or AGGREGATE totals nested inside the range, which helps when the column has subtotal rows in it.

It is the cleanest choice: no Ctrl+Shift+Enter, any error type, and it works in every current version. The one limit is that functions 1 to 13 need a real range. =AGGREGATE(9,6,F2:F9*2) returns #VALUE!, because F2:F9*2 is a calculated array. Only 14 to 19 (LARGE, SMALL, PERCENTILE, QUARTILE) accept arrays like that.

MAX, MIN and AVERAGE with errors in the range

Profit per unit in G has both kinds of error: #N/A for the spice rack and #DIV/0! for the tea towels. =MAX(G2:G9) gives #DIV/0!. Change AGGREGATE's first argument and the errors drop out: =AGGREGATE(5,6,G2:G9) gives the lowest, 3.85 for the spatula, and =AGGREGATE(1,6,G2:G9) the average, 20.3 across the six products with a number.

To name the winner, feed the result to MATCH: =INDEX(B2:B9,MATCH(AGGREGATE(4,6,G2:G9),G2:G9,0)) returns Dutch oven. MATCH does not mind errors in the range it searches.

SUM(IFERROR): skip an error or count it as 0

IFERROR can turn every error in the range into something else before SUM sees it. =SUM(IFERROR(F2:F9,0)) gives 4,115.25, the same as AGGREGATE. In Excel 365 and 2021 you just press Enter. In Excel 2019 and older it is an array formula, so confirm it with Ctrl+Shift+Enter or you get the wrong answer. Unlike AGGREGATE it also works on a calculated array, such as IFERROR(F2:F9*2,0).

For a sum, replacing errors with 0 is harmless. For an average it changes the answer, because each 0 counts as one more value. The tea towels did not earn 0 per unit; they sold none, so there is no per-unit figure at all. Skipping the errors with =AVERAGE(IFERROR(G2:G9,"")) gives 20.3, because AVERAGE ignores text. Turning them into 0 drags the average down to 15.225. Use "" when an error means "no data" and 0 only when zero is the real value.

One more trap with this column: an average of per-unit figures treats the 15 Dutch ovens and the 118 peeler sets as equally important. Total profit divided by total units, =AGGREGATE(9,6,F2:F9)/SUMIF(E2:E9,"<>#N/A",C2:C9), gives 11.37 per unit across the products with a cost.

Why SUMIF(range,"<>#N/A") is not enough

A popular fix is =SUMIF(F2:F9,"<>#N/A"). On column F it works and returns 4,115.25, because the only error there is #N/A. SUMIF treats the criterion as "add every cell that is not #N/A", and errors of any other type still pass the test and get added.

Column G has a #DIV/0! as well, so the same formula breaks. You can list each error type with SUMIFS, =SUMIFS(G2:G9,G2:G9,"<>#N/A",G2:G9,"<>#DIV/0!") gives 121.8, but the first #VALUE! that turns up next month breaks it again. AGGREGATE has no such list to keep up to date.

Better: fix the error at its source

Every method above hides a problem rather than solving it. The 4,115.25 total quietly leaves out the spice rack, which brought in 667 of revenue. That is fine for a quick look and wrong in a report. Put a check next to any total that skips errors, so the reader knows something is missing.

Then fix what you can at the source. Add KT-107 to the cost table, say at 14.5, and its profit becomes 333.5 and the plain SUM gives 4,448.75 with no tricks. For a division that can hit zero, write the guard into the formula: =IF(C2=0,"",F2/C2) leaves the tea towels' cell empty-looking, and SUM, MAX and AVERAGE skip text automatically. Do not wrap the total itself in IFERROR: =IFERROR(SUM(F2:F9),0) shows 0, which looks like a real result.