How to How to Use AGGREGATE Function in Excel
Learn to master the AGGREGATE function, which performs calculations (SUM, AVERAGE, COUNT, etc.) while ignoring errors and hidden rows. This powerful function eliminates the need for complex nested formulas and enhances data analysis efficiency. You'll discover how to use 19 different functions and 6 option parameters to handle real-world messy datasets professionally.
Why This Matters
AGGREGATE handles errors and hidden data automatically, saving time and preventing spreadsheet errors in financial reporting and data analysis tasks.
Prerequisites
- •Basic understanding of Excel formulas and functions
- •Familiarity with basic mathematical functions (SUM, AVERAGE, COUNT)
- •Knowledge of cell references and ranges
Step-by-Step Instructions
Open a blank Excel worksheet
Launch Excel and create a new blank workbook. Enter sample data in column A (numbers 10, 15, #DIV/0!, 25, 30) to practice with errors and values.
Click on the target cell for your formula
Select an empty cell (e.g., B1) where you want the AGGREGATE result to appear. This is where your formula output will display.
Type the AGGREGATE formula syntax
Enter the formula: =AGGREGATE(function_number, option_number, range). For example, =AGGREGATE(9,6,A1:A5) calculates SUM while ignoring errors and hidden rows.
Select the function number (1-19)
Use function numbers: 1=AVERAGE, 2=COUNT, 3=COUNTA, 9=SUM, 15=SMALL, 16=LARGE. Refer to Excel documentation for all 19 available functions in Formulas > Function Library.
Choose the option number (0-6) and press Enter
Select: 0=ignore nothing, 6=ignore hidden rows/error values (most common). Press Enter to execute the formula and view your calculated result.
Alternative Methods
Using IFERROR with traditional functions
Wrap SUM, AVERAGE, or COUNT with IFERROR to handle errors: =IFERROR(SUM(A1:A5),0). Less flexible than AGGREGATE but works for simple error handling.
Manually filtering and using subtotals
Use Data > Subtotals menu to auto-calculate filtered ranges. This is slower and less flexible than AGGREGATE for complex datasets.
Tips & Tricks
- ✓Use option 6 most frequently—it ignores both hidden rows and error values, the most common requirement for data cleanup.
- ✓AGGREGATE works with entire columns (e.g., A:A) without calculation loops, unlike some array formulas.
- ✓Test your formula with intentional errors in your data range to verify it handles exceptions correctly.
Pro Tips
- ★Combine AGGREGATE with OFFSET or INDIRECT for dynamic ranges that automatically adjust as data grows.
- ★Use function 15 (SMALL) or 16 (LARGE) with AGGREGATE to find nth smallest/largest values while ignoring errors.
- ★AGGREGATE ignores SUBTOTAL and other AGGREGATE functions within its range, preventing circular calculations.
Troubleshooting
Check that your function_number is between 1-19 and option_number is 0-6. Verify the range reference is valid and contains appropriate data types.
Ensure you're using option 5 or 6 if you want to ignore hidden rows; options 0-4 include hidden data in calculations.
Change option_number to 6 to automatically ignore error values, or use options 1-4 only if your data is error-free.
Related Excel Formulas
Frequently Asked Questions
What's the difference between AGGREGATE and SUBTOTAL?
Can AGGREGATE work with filtered data?
Which function number should I use for averaging?
Is AGGREGATE available in older Excel versions?
This was one task. ElyxAI handles hundreds.
Sign up