5 real‑world examples · from basic to advanced
AGGREGATE is Excel’s most versatile aggregation function. It performs calculations like SUM, AVERAGE, MAX, MIN, and more — while ignoring errors, hidden rows, and subtotals. It’s the Swiss Army knife for robust data analysis. These 5 examples use real business scenarios with separate data and formula tables for clarity.
1. Sum with errors ignored
BASIC Data CleansingCalculate the sum of values while ignoring #VALUE! and #N/A errors.
📊 Sales Data (with errors)
A:A
| A | B | |
|---|---|---|
| 1 | Revenue | Notes |
| 2 | 45,200 | |
| 3 | #VALUE! | Error |
| 4 | 38,750 | |
| 5 | #N/A | Missing |
| 6 | 52,100 | |
| 7 | 29,800 |
📝 Aggregated results
=AGGREGATE(9, 6, A:A)
| D | E | F | |
|---|---|---|---|
| 1 | Calculation | Result | Formula |
| 2 | SUM (ignore errors) | 165,850 | =AGGREGATE(9, 6, A:A) |
| 3 | AVERAGE (ignore errors) | 41,462.50 | =AGGREGATE(1, 6, A:A) |
| 4 | COUNT (ignore errors) | 4 | =AGGREGATE(2, 6, A:A) |
=AGGREGATE(9, 6, A:A)
SUM (ignoring errors):
165,850
(excludes #VALUE! and #N/A)
How it works:
AGGREGATE(function_num, options, range) — 9 = SUM, 6 = ignore errors. This gives a clean total without the need for IFERROR or SUMIFS.
Real‑world use: Data analysts use AGGREGATE to create robust reports that don’t break when source data contains errors from imports or formulas.
2. Ignore hidden rows in filtered data
BASIC ReportingCalculate averages and sums on visible rows only — even when rows are hidden.
📊 Employee Data (some rows hidden)
A:C
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Department | Salary |
| 2 | Alice | IT | 75,000 |
| 3″ | Bob | Finance | 68,000 |
| 4 | Carol | HR | 62,000 |
| 5″ | Dave | IT | 82,000 |
| 6 | Eve | Sales | 71,000 |
| 7 | Frank | Finance | 65,000 |
† Rows 3 and 5 are hidden (e.g., filtered out)
📝 Aggregated on visible rows
=AGGREGATE(1, 5, C:C)
| E | F | G | |
|---|---|---|---|
| 1 | Calculation | Result | Formula |
| 2 | AVERAGE (visible only) | 68,250 | =AGGREGATE(1, 5, C:C) |
| 3 | MAX (visible only) | 75,000 | =AGGREGATE(4, 5, C:C) |
| 4 | COUNT (visible only) | 4 | =AGGREGATE(2, 5, C:C) |
=AGGREGATE(1, 5, C:C)
AVERAGE of visible:
68,250
(excludes hidden rows)
How it works:
options = 5 ignores hidden rows. This is perfect for filtered lists or manually hidden rows — the calculation only includes visible data.
Real‑world use: Financial analysts use AGGREGATE with filtered data to see subtotals of visible rows without including rows that are filtered out.
3. Ignore both errors AND hidden rows
INTERMEDIATE Data QualityCombine error‑ignoring and hidden‑row‑ignoring for maximum robustness.
📊 Inventory Data (errors + hidden rows)
A:A
| A | B | |
|---|---|---|
| 1 | Stock Qty | Status |
| 2 | 150 | |
| 3″ | #VALUE! | Hidden error |
| 4 | 220 | |
| 5″ | 180 | Hidden row |
| 6 | #N/A | Error |
| 7 | 95 |
† Rows 3 and 5 are hidden
📝 Aggregated with both options
=AGGREGATE(9, 7, A:A)
| D | E | F | |
|---|---|---|---|
| 1 | Calculation | Result | Formula |
| 2 | SUM (ignore errors + hidden) | 465 | =AGGREGATE(9, 7, A:A) |
| 3 | AVERAGE (ignore errors + hidden) | 155 | =AGGREGATE(1, 7, A:A) |
| 4 | MIN (ignore errors + hidden) | 95 | =AGGREGATE(5, 7, A:A) |
=AGGREGATE(9, 7, A:A)
SUM (errors + hidden ignored):
465
(only 150 + 220 + 95)
How it works:
options = 7 ignores both hidden rows AND error values. This is the most robust option for messy data with multiple issues.
Real‑world use: Data quality teams use AGGREGATE with option 7 to get reliable aggregates from raw data that may contain errors and filtered/hidden rows.
4. Ignore subtotals (nested aggregations)
INTERMEDIATE Financial ReportingCalculate grand totals that ignore subtotal rows in a report.
📊 P&L Report (with subtotals)
B:B
| A | B | |
|---|---|---|
| 1 | Category | Amount |
| 2 | Revenue – Product A | 45,000 |
| 3 | Revenue – Product B | 32,500 |
| 4″ | Subtotal Revenue | 77,500 |
| 5 | Cost – Product A | 28,000 |
| 6 | Cost – Product B | 19,000 |
| 7″ | Subtotal Cost | 47,000 |
| 8″ | Gross Profit | 30,500 |
† Subtotal rows generated with SUBTOTAL or manual formulas
📝 Grand totals ignoring subtotals
=AGGREGATE(9, 3, B:B)
| D | E | F | |
|---|---|---|---|
| 1 | Calculation | Result | Formula |
| 2 | SUM (ignore subtotals) | 124,500 | =AGGREGATE(9, 3, B:B) |
| 3 | AVERAGE (ignore subtotals) | 31,125 | =AGGREGATE(1, 3, B:B) |
| 4 | COUNT (ignore subtotals) | 4 | =AGGREGATE(2, 3, B:B) |
=AGGREGATE(9, 3, B:B)
Grand total (subtotals ignored):
124,500
(excludes 77,500 + 47,000 + 30,500)
How it works:
options = 3 ignores rows with subtotal formulas. This prevents double-counting when you have subtotals within your data.
Real‑world use: Financial controllers use AGGREGATE with option 3 to calculate grand totals in P&L statements, balance sheets, and other reports with nested subtotals.
5. Advanced: AGGREGATE with array operations
ADVANCED Complex AnalysisUse AGGREGATE with array constants and multiple functions in one formula.
📊 Sales by Region & Quarter
A:D
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 |
| 2 | North | 245 | #DIV/0! | 310 |
| 3 | South | 180 | 220 | #VALUE! |
| 4 | East | #N/A | 195 | 260 |
| 5 | West | 310 | 290 | 350 |
📝 Multi-function aggregate summary
=AGGREGATE({9,1,4,5}, 6, B:D)
| F | G | H | I | |
|---|---|---|---|---|
| 1 | Metric | All Sales | North | Formula |
| 2 | SUM | 2,360 | 555 | =AGGREGATE(9, 6, B2:D2) |
| 3 | AVERAGE | 262.22 | 277.5 | =AGGREGATE(1, 6, B2:D2) |
| 4 | MAX | 350 | 310 | =AGGREGATE(4, 6, B2:D2) |
| 5 | MIN | 180 | 245 | =AGGREGATE(5, 6, B2:D2) |
| 6 | COUNT | 9 | 2 | =AGGREGATE(2, 6, B2:D2) |
=AGGREGATE(9, 6, B2:D2)
=AGGREGATE({9,1,4,5,2}, 6, B:D)
All sales SUM (errors ignored):
2,360
(from 9 valid cells)
How it works: The array constant
{9,1,4,5} returns multiple aggregation results. AGGREGATE can accept arrays for the function_num parameter, making it powerful for summary reports. options = 6 ignores errors across the entire array.
Real‑world use: Business intelligence teams use AGGREGATE with array constants to create multi-metric summaries in a single formula, reducing spreadsheet complexity.
💡 Pro Tip: AGGREGATE is available in Excel 2010+. The function_num options: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV.S, 8=STDEV.P, 9=SUM, 10=VAR.S, 11=VAR.P, 12=MEDIAN, 13=MODE.SNGL, 14=LARGE, 15=SMALL, 16=PERCENTILE.INC, 17=QUARTILE.INC, 18=PERCENTILE.EXC, 19=QUARTILE.EXC.
AGGREGATE Syntax
=AGGREGATE(function_num, options, range, [k])
function_num – 1-19 (AVERAGE, SUM, COUNT, MAX, MIN, etc.)
options – 0-7 (ignore errors, hidden rows, subtotals)
range – the data range to aggregate
k – required for LARGE, SMALL, PERCENTILE, QUARTILE
Options: 0=ignore no values, 1=ignore hidden rows, 2=ignore errors, 3=ignore hidden rows & errors, 4=ignore nothing, 5=ignore hidden rows, 6=ignore errors, 7=ignore hidden rows & errors.
⚠️ Key Benefits: AGGREGATE replaces SUBTOTAL, SUMIF, AVERAGEIF, etc., with a single, more powerful function that handles errors and hidden rows elegantly.
⚠️ Key Benefits: AGGREGATE replaces SUBTOTAL, SUMIF, AVERAGEIF, etc., with a single, more powerful function that handles errors and hidden rows elegantly.
AGGREGATE vs. Other Functions
AGGREGATE
Multi-function, robust
Ignores errors & hidden rows
Multi-function, robust
Ignores errors & hidden rows
SUBTOTAL
Basic, limited
No error handling
Basic, limited
No error handling
SUM/AVERAGE
Simple aggregation
Breaks on errors
Simple aggregation
Breaks on errors
Best Use
Dirty data, reports, dashboards
Dirty data, reports, dashboards
