AGGREGATE

Why it works

The options argument decides what is skipped before the calculation starts, so the SUM only ever sees numbers. The calculation itself is the ordinary SUM.

More to watch out for

• Option 6 does not skip rows hidden by a filter. Use 7 if the visible rows are what you want.

Variations

=SUM(IFERROR(C2:C7, 0))

=SUM(IFERROR(C2:C7; 0))

Why it works

Codes 14 to 19 take a fourth argument, k, which is read after the errors have been removed. Rank therefore counts only real numbers, and a duplicate amount occupies as many ranks as it has entries.

More to watch out for

• Leave out k with code 14 and AGGREGATE returns #VALUE!, because LARGE has no default rank.

Variations

=LARGE(IF(ISNUMBER(B2:B8), B2:B8), D2)

=LARGE(IF(ISNUMBER(B2:B8); B2:B8); D2)

=LARGE(FILTER(B2:B8, ISNUMBER(B2:B8)), D2)

=LARGE(FILTER(B2:B8; ISNUMBER(B2:B8)); D2)

Why it works

Dividing by a condition turns every non-matching row into an error, and the error-skipping option then acts as the filter. The array form of AGGREGATE evaluates such a calculation itself, so no Ctrl+Shift+Enter is needed.

More to watch out for

• If no row matches, every value is an error and the result is #NUM!. That is correct but not a clear message; wrap the formula in IFERROR only if you can tell 'no match' from a real problem.
• Hidden rows are not skipped here, even with option 7, because the array after the division carries no row information.

Variations

=MAXIFS(C2:C8, A2:A8, E2)

=MAXIFS(C2:C8; A2:A8; E2)

=MAX(FILTER(C2:C8, A2:A8 = E2))

=MAX(FILTER(C2:C8; A2:A8 = E2))

Why it works

LET names the range once, so both quartiles read the same reference and each AGGREGATE call applies option 7 to it. Option 7 combines two filters that a plain QUARTILE.INC cannot offer, and codes 14 to 19 are the only ones that give percentiles and quartiles with that choice of skipping.

Watch out for

• A different mistake: with option 6 instead of 7 the hidden 52000 counts. Q1 becomes 1387.50, Q3 5755 and the fence 12306.25. The formula still returns a number, so nothing signals the error.

  For the same reason, record the view in which you ran the test: unhiding a row or clearing a filter changes the result without a warning.

• Option 4 or 5 does not skip the #N/A, so the whole formula returns #N/A.

• On Microsoft 365 or Excel 2021, FILTER(B2:B9, ISNUMBER(B2:B9)) gives a visible cleaned list that you can reuse for any statistic, but it does not recognize hidden rows.

  The AGGREGATE part of this formula works from Excel 2010 and sees hidden rows, while the LET around it needs Excel 2021 or Microsoft 365 (without LET, repeat B2:B9 in both calls).

  If the errors come from a lookup, wrap the lookup in IFNA instead, so the problem is dealt with where it starts.

Variations

=LET(
  amounts, B2:B9,
  lowq, AGGREGATE(17, 6, amounts, 1),
  highq, AGGREGATE(17, 6, amounts, 3),
  ROUND(highq + 1.5 * (highq - lowq), 2)
)

=LET(
  amounts; B2:B9;
  lowq; AGGREGATE(17; 6; amounts; 1);
  highq; AGGREGATE(17; 6; amounts; 3);
  ROUND(highq + 1,5 * (highq - lowq); 2)
)