How to Use AGGREGATE Function in Excel + Tips
Learn how to use AGGREGATE function in Excel step-by-step to calculate SUM, AVERAGE, MAX, LARGE while ignoring errors and hidden rows.
Hello everyone! Today i will show you how to use AGGREGATE function in Microsoft Excel step by step.
1. What is AGGREGATE Function in Excel?
The AGGREGATE function is one of the most powerful math & statistical formulas in Excel. It allows you to perform 19 different calculations (such as SUM, AVERAGE, COUNT, LARGE, SMALL) while automatically ignoring error values (#DIV/0!, #N/A, #VALUE!) and hidden rows from filters.
Unlike standard SUM or AVERAGE formulas that crash when encountering error cells, AGGREGATE skips errors smoothly!
📚 Related Tutorial: Need a simple formula for filtered data? Check out How to Use SUBTOTAL Function in Excel!
2. 🛠️ Formula Syntax & Parameters
AGGREGATE has two forms depending on which function you choose:
Form 1: Reference Form (for functions 1 to 13)
=AGGREGATE(function_num, options, ref1, [ref2], ...)
Form 2: Array Form (for functions 14 to 19)
=AGGREGATE(function_num, options, array, k)
function_num: Required. A number from1to19specifying which calculation to perform (e.g.,9=SUM,1=AVERAGE,14=LARGE).options: Required. A number from0to7specifying what values Excel should ignore (e.g.,6= ignore errors,3= ignore hidden rows & errors).ref1/array: Required. The range of cells to perform calculation on (e.g.,B2:B6).k: Required only for array functions (14to19). Represents the k-th position (e.g.,k = 2for 2nd largest value).
3. Reference Tables
Function Number Reference (function_num)
function_num | Function Name | Description | Syntax Form |
|---|---|---|---|
| 1 | AVERAGE | Calculates the average of values | Reference |
| 2 | COUNT | Counts cells with numbers | Reference |
| 3 | COUNTA | Counts non-empty cells | Reference |
| 4 | MAX | Finds the maximum (largest) value | Reference |
| 5 | MIN | Finds the minimum (smallest) value | Reference |
| 6 | PRODUCT | Multiplies all numbers together | Reference |
| 7 | STDEV.S | Calculates sample standard deviation | Reference |
| 8 | STDEV.P | Calculates population standard deviation | Reference |
| 9 | SUM | Calculates the total sum of values | Reference |
| 10 | VAR.S | Calculates sample variance | Reference |
| 11 | VAR.P | Calculates population variance | Reference |
| 12 | MEDIAN | Calculates the median value | Reference |
| 13 | MODE.SNGL | Calculates the most frequent value | Reference |
| 14 | LARGE | Finds the k-th largest value | Array (requires k) |
| 15 | SMALL | Finds the k-th smallest value | Array (requires k) |
| 16 | PERCENTILE.INC | Calculates k-th percentile (inclusive) | Array (requires k) |
| 17 | QUARTILE.INC | Calculates k-th quartile (inclusive) | Array (requires k) |
| 18 | PERCENTILE.EXC | Calculates k-th percentile (exclusive) | Array (requires k) |
| 19 | QUARTILE.EXC | Calculates k-th quartile (exclusive) | Array (requires k) |
Options Reference (options)
options | Description | Typical Use Case |
|---|---|---|
| 0 | Ignore nested SUBTOTAL and AGGREGATE functions | Safe sub-totaling without double counting |
| 1 | Ignore nested SUBTOTAL/AGGREGATE functions and hidden rows | Filtered data sub-totaling |
| 2 | Ignore error values only (#DIV/0!, #N/A, #VALUE!, etc.) | Ranges containing formula error cells |
| 3 | Ignore hidden rows, error values, and nested SUBTOTAL/AGGREGATE | Complete cleanup for filtered tables |
| 4 | Ignore nothing | Calculate everything standard |
| 5 | Ignore hidden rows only | Filtered data without error handling |
| 6 | Ignore error values only | Simple error cleanup |
| 7 | Ignore nested SUBTOTAL/AGGREGATE functions and error values | Multi-level subtotaling with errors |
4. 📋 Practical Examples (Follow Along)
To test these examples, create this sample table in range A1:B6:
| Row | Column A (Item) | Column B (Sales) |
|---|---|---|
| 1 | Item | Sales |
| 2 | Item A | $500 |
| 3 | Item B | #DIV/0! (Error) |
| 4 | Item C | $800 |
| 5 | Item D (Hidden) | $300 |
| 6 | Item E | $400 |
Example 1: Sum Numbers Ignoring #DIV/0! Errors
Standard =SUM(B2:B6) will break and return #DIV/0!. Using AGGREGATE with function_num = 9 (SUM) and options = 6 (Ignore Errors):
=AGGREGATE(9, 6, B2:B6)
👉 Result: $2,000 ($500 + $800 + $300 + $400). Excel skips the error cell automatically!
Example 2: Sum Visible Rows Ignoring Both Errors and Hidden Rows
Suppose Row 5 (Item D) is hidden by a filter or manually hidden. Use function_num = 9 (SUM) and options = 3 (Ignore Hidden Rows & Errors):
=AGGREGATE(9, 3, B2:B6)
👉 Result: $1,700 ($500 + $800 + $400). It ignores both the #DIV/0! error and hidden Row 5!
Example 3: Find 2nd Largest Value (LARGE) Ignoring Errors
Standard =LARGE(B2:B6, 2) fails due to the #DIV/0! error. Use function_num = 14 (LARGE), options = 6 (Ignore Errors), and k = 2:
=AGGREGATE(14, 6, B2:B6, 2)
👉 Result: $500 (2nd largest number after $800).
💡 Quick Tips for Using AGGREGATE Function
- Option
6for Quick Error Cleanup: Useoptions = 6whenever you need to calculate totals or averages on data containing#N/A,#DIV/0!,#VALUE!, or#REF!errors without breaking your formula. - Option
3for Filtered Tables with Errors: Option3is the ultimate cheat code for filtered data tables because it ignores hidden rows, errors, and nested subtotals all at once. - No Need for Complex
IFERRORWrappers: Instead of writing complex array formulas like=SUM(IFERROR(B2:B6, 0)), simply write=AGGREGATE(9, 6, B2:B6)! - Remember
kArgument for Functions 14 to 19: Functions14(LARGE),15(SMALL), and16-19require a 4th argumentk(e.g.,k = 1for 1st largest). For functions1to13, leavekempty.
📚 Related Resources & Tutorials
Related Tutorials
Opens in new tab ↗How to Use LET Function in Excel + Tips
Learn how to use LET function in Excel step-by-step to define variables in formulas with simple examples, benefits, and tips.
How to Use LAMBDA Function in Excel + Tips
Learn how to use LAMBDA function in Excel step-by-step to define variables in formulas with simple examples, benefits, and tips.
How to Use DROP Function in Excel + Tips
Learn how to use DROP function in Excel step-by-step to remove specified rows or columns from the start or end of a range with simple examples and tips.