The AGGREGATE function in Excel is a powerful and versatile tool that combines 19 different calculation functions into one, with the ability to ignore hidden rows, errors, and nested subtotals. This tutorial will guide you through its usage with clear examples and practical applications.
What Makes AGGREGATE Special?
Unlike standard Excel functions, AGGREGATE offers unique advantages:
- Error handling: Can ignore error values (#DIV/0!, #N/A, etc.)
- Hidden rows: Can exclude hidden rows from calculations
- Nested SUBTOTAL: Can ignore other SUBTOTAL and AGGREGATE functions
- 19 functions in one: Replaces AVERAGE, COUNT, MAX, MIN, SUM, and more
Syntax
AGGREGATE(function_num, options, ref1, [ref2], …)
- function_num: A number 1-19 that specifies which function to use (see table below).
- options: A number 0-7 that determines which values to ignore.
- ref1: The first numeric argument for the function.
- ref2, …: Optional additional numeric arguments (for functions 14-19).
Function Numbers (function_num)
| Number | Function | Description |
|---|---|---|
| 1 | AVERAGE | Calculate average |
| 2 | COUNT | Count numbers |
| 3 | COUNTA | Count non-empty cells |
| 4 | MAX | Find maximum value |
| 5 | MIN | Find minimum value |
| 6 | PRODUCT | Multiply values |
| 7 | STDEV.S | Standard deviation (sample) |
| 8 | STDEV.P | Standard deviation (population) |
| 9 | SUM | Sum values |
| 10 | VAR.S | Variance (sample) |
| 11 | VAR.P | Variance (population) |
| 12 | MEDIAN | Find median value |
| 13 | MODE.SNGL | Find most common value |
| 14 | LARGE | Kth largest value |
| 15 | SMALL | Kth smallest value |
| 16 | PERCENTILE.INC | Percentile (inclusive) |
| 17 | QUARTILE.INC | Quartile (inclusive) |
| 18 | PERCENTILE.EXC | Percentile (exclusive) |
| 19 | QUARTILE.EXC | Quartile (exclusive) |
Options Numbers (options)
| Number | Behavior |
|---|---|
| 0 or omitted | Ignore nested SUBTOTAL and AGGREGATE functions |
| 1 | Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions |
| 2 | Ignore error values, nested SUBTOTAL and AGGREGATE functions |
| 3 | Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE |
| 4 | Ignore nothing |
| 5 | Ignore hidden rows |
| 6 | Ignore error values |
| 7 | Ignore hidden rows and error values |
AGGREGATE Function Examples
Example 1: Average Ignoring Errors
To calculate average of A1:A10 while ignoring error values:
=AGGREGATE(1, 6, A1:A10)
This uses function 1 (AVERAGE) with option 6 (ignore errors).
Example 2: Sum Visible Cells Only
To sum only visible cells in B1:B20 (excluding hidden rows):
=AGGREGATE(9, 5, B1:B20)
This uses function 9 (SUM) with option 5 (ignore hidden rows).
Example 3: Maximum Value Ignoring Errors and Hidden Rows
To find the maximum value in C1:C50 while ignoring errors and hidden rows:
=AGGREGATE(4, 7, C1:C50)
This uses function 4 (MAX) with option 7 (ignore both).
Example 4: Third Largest Value
To find the 3rd largest value in D1:D100:
=AGGREGATE(14, 6, D1:D100, 3)
This uses function 14 (LARGE) with k=3 to get the 3rd largest value, ignoring errors.
Example 5: Median of Visible Cells
To calculate the median of visible cells in E1:E30:
=AGGREGATE(12, 5, E1:E30)
This uses function 12 (MEDIAN) with option 5 (ignore hidden rows).
Interactive AGGREGATE Demo
Enter values (comma-separated, use “error” for error values):
Practical Applications
Common Use Cases:
- Filtered data analysis: Calculate statistics on filtered data without errors
- Error-proof formulas: Perform calculations even when source data contains errors
- Dynamic reports: Create reports that automatically exclude hidden rows
- Top/bottom analysis: Find top N or bottom N values while handling errors
- Clean data summaries: Summarize data while ignoring problematic cells
- Pivot table alternatives: Create flexible summary calculations
Real-World Business Examples
Business Case 1: Sales Performance (Top 5)
Scenario: Find the 5th highest sales value in a dataset that may contain errors:
=AGGREGATE(14, 6, A2:A100, 5)
Result: Returns the 5th largest sales value, ignoring any error cells.
Business Case 2: Average Revenue of Visible Products
Scenario: Calculate average revenue when some products are filtered out:
=AGGREGATE(1, 5, B2:B200)
Result: Average of only visible (not hidden/filtered) rows.
Business Case 3: Error-Safe Maximum
Scenario: Find maximum value even if some calculations resulted in errors:
=AGGREGATE(4, 6, C2:C500)
Result: Maximum value, completely ignoring #DIV/0!, #N/A, and other errors.
Business Case 4: Percentile Analysis
Scenario: Find the 75th percentile of test scores, excluding errors:
=AGGREGATE(16, 6, D2:D50, 0.75)
Result: 75th percentile value, ignoring any error values in the range.
AGGREGATE vs Similar Functions
| Feature | AGGREGATE | SUBTOTAL | Standard Functions |
|---|---|---|---|
| Ignore errors | ✓ Yes | ✗ No | ✗ No |
| Ignore hidden rows | ✓ Yes | ✓ Yes | ✗ No |
| Number of functions | 19 | 11 | 1 each |
| LARGE/SMALL support | ✓ Yes | ✗ No | ✓ Yes |
| Flexibility | Very High | Medium | Low |
Tips and Tricks
- Error handling: Use option 6 or 7 to create error-proof dashboards and reports.
- Filtered lists: Use option 5 or 7 when working with filtered data to get accurate results.
- Dynamic ranges: Combine with dynamic ranges for flexible reporting.
- Nested formulas: AGGREGATE ignores other AGGREGATE and SUBTOTAL functions by default.
- Function 14-19: These require an additional k or percentile argument.
- Text values: Non-numeric values are ignored in calculations.
- Performance: AGGREGATE is efficient even with large datasets.
- Array formulas: Can be used in array formulas for advanced calculations.
Advanced Techniques
Technique 1: Top 3 Average
Calculate the average of the top 3 values in a range with errors:
=AVERAGE(AGGREGATE(14, 6, A1:A20, {1,2,3}))
This gets the 1st, 2nd, and 3rd largest values, then averages them.
Technique 2: Exclude Outliers
Calculate average excluding the highest and lowest values:
=(AGGREGATE(9, 6, A1:A10) – AGGREGATE(4, 6, A1:A10) – AGGREGATE(5, 6, A1:A10)) / (AGGREGATE(2, 6, A1:A10) – 2)
This subtracts max and min from sum, then divides by count minus 2.
Technique 3: Dynamic Nth Largest
Find the Nth largest value where N is in cell C1:
=AGGREGATE(14, 6, A1:A100, C1)
This creates a dynamic ranking formula that updates when C1 changes.