The Big Problem: Why Standard SUM & SUBTOTAL Break
In real-world data analysis, datasets are rarely clean. Formulas like VLOOKUP or division calculations often leave #N/A or #DIV/0! error cells.
❌ STANDARD =SUM() & =SUBTOTAL()
Formula Crash (#DIV/0!)
If even ONE single cell in your range contains #DIV/0! or #N/A, standard SUM and SUBTOTAL immediately crash and return an error!
✅ =AGGREGATE(9, 6, Range)
Gracefully Bypasses Errors
AGGREGATE ignores all error cells and calculates the exact total of valid numbers without breaking your dashboard.
Formula Syntax & Dual Forms
AGGREGATE comes in two flexible forms depending on the function type:
Form 1: Reference Form (for SUM, AVERAGE, MAX, MIN...)
=AGGREGATE(function_num, options, ref1, [ref2], ...)Form 2: Array Form (for LARGE, SMALL, PERCENTILE...)
=AGGREGATE(function_num, options, array, k)Interactive Error Bypass Simulator
The dataset below contains intentional #DIV/0! and #N/A errors. Select different options to see how AGGREGATE saves your report!
Standard =SUM(Sales)#DIV/0!❌ Crashes due to error cells
=SUBTOTAL(9, Sales)#DIV/0!❌ Crashes due to error cells
=AGGREGATE(9, 6, Sales)$3,250✅ Bypasses errors seamlessly!
| Cell Label | Category | Cell Value | Evaluation Status |
|---|---|---|---|
| MacBook Pro Sales | Hardware | $1800 | ✅ Included in Calculation |
| Division Margin Discount | Software | #DIV/0! | 🛡️ Ignored by Option 6 |
| 4K Monitor Sales | Hardware | $650 | ✅ Included in Calculation |
| Missing Inventory Stock | Hardware | #N/A | 🛡️ Ignored by Option 6 |
| Wireless Mouse Sales | Hardware | $150 | ✅ Included in Calculation |
| Corrupted Currency Cell | Other | #VALUE! | 🛡️ Ignored by Option 6 |
| Standing Desk Sales | Furniture | $400 | ✅ Included in Calculation |
| Ergonomic Chair Sales | Furniture | $250 | ✅ Included in Calculation |
19 Supported Functions List
AGGREGATE supports 19 calculation functions — far more than SUBTOTAL!
#1AVERAGERef
#2COUNTRef
#3COUNTARef
#4MAXRef
#5MINRef
#6PRODUCTRef
#7STDEV.SRef
#8STDEV.PRef
#9SUMRef
#10VAR.SRef
#11VAR.PRef
#12MEDIANRef
#13MODE.SNGLRef
#14LARGEArray (requires k)
#15SMALLArray (requires k)
#16PERCENTILE.INCArray (requires k)
#17QUARTILE.INCArray (requires k)
#18PERCENTILE.EXCArray (requires k)
#19QUARTILE.EXCArray (requires k)
Options Codes Reference Table (0 to 7)
| Option Code | What it Ignores | Best Used For |
|---|---|---|
| Option 0 | Ignore nested SUBTOTAL and AGGREGATE functions | Standard Option |
| Option 1 | Ignore hidden rows, nested SUBTOTAL and AGGREGATE | Standard Option |
| Option 2 | Ignore error values, nested SUBTOTAL and AGGREGATE | Standard Option |
| Option 3 | Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE | ⭐ Best for Filtered & Error Tables |
| Option 4 | Ignore nothing (Standard calculation) | Standard (Ignores nothing) |
| Option 5 | Ignore hidden rows only | Standard Option |
| Option 6 | Ignore error values only (MOST POPULAR!) | ⭐ Most Popular for Error Bypass |
| Option 7 | Ignore hidden rows and error values | Standard Option |
🧪 Knowledge Check — AGGREGATE Quiz
Question 1 of 5Score: 0