AGGREGATE performs calculations like SUM, AVERAGE, or MAX while optionally ignoring errors and hidden rows. It is like SUBTOTAL but more powerful because it can skip error values and supports more functions.
=AGGREGATE(function_num, options, ref1, [ref2 or k], …)What does AGGREGATE do?
AGGREGATE is a built-in Excel function that calculates a result from a range or array, similar to SUBTOTAL, but with extra abilities. You choose a function number (such as 9 for SUM or 1 for AVERAGE) and an options code that tells Excel what to ignore—errors, hidden rows, nested SUBTOTALs, or nothing. This makes it ideal for messy data where some cells contain #DIV/0! or #N/A.
Why does it matter? In real spreadsheets, errors happen. A single #VALUE! can break a SUM or AVERAGE. AGGREGATE lets you get a clean total or average without cleaning the data first. It also works with filtered lists, so you can calculate only visible rows.
Imagine a sales report where one region has a missing target and shows #N/A. You still need the total sales. With AGGREGATE you write =AGGREGATE(9,6,B2:B10) and get the sum of all numbers, ignoring the error. No helper columns, no manual fixes.
When to use AGGREGATE
- Summing a column that contains error values like #N/A or #DIV/0!.
- Averaging visible rows only after applying a filter.
- Finding the maximum or minimum while ignoring hidden rows.
- Counting numbers in a range that includes errors.
- Calculating statistics on a filtered table without using SUBTOTAL.
- Getting the second largest value with the LARGE function while skipping errors.
AGGREGATE syntax
=AGGREGATE(function_num, options, ref1, [ref2 or k], …)| Argument | Required? | What to enter |
|---|---|---|
function_num | Required | A number from 1 to 19 that specifies which function to use. For example, 1 = AVERAGE, 9 = SUM, 4 = MAX. Enter the number, not the name. |
options | Required | A number from 0 to 7 that controls what to ignore. Common choices: 6 = ignore error values, 5 = ignore hidden rows, 7 = ignore both errors and hidden rows. |
ref1 | Required | The first range or value to calculate. This is usually a column of numbers, like B2:B10. |
ref2 or k | Optional | Additional ranges (up to 253) or a parameter for functions like LARGE, SMALL, PERCENTILE, QUARTILE, or TRIMMEAN. For LARGE, k is the position (e.g., 2 for second largest). |
What it returns: AGGREGATE returns a single number: the result of the chosen function applied to the referenced data, after ignoring the specified items (errors, hidden rows, etc.).
AGGREGATE examples
1 Sum sales while ignoring errors
You have monthly sales in column B, but one cell contains #N/A. The goal is to total all valid sales. The formula =AGGREGATE(9,6,B2:B7) uses function_num 9 (SUM) and options 6 (ignore errors). Excel adds the numbers and skips the error, returning 4500.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | |||
| 2 | Jan | 1000 | 4500 | ||
| 3 | Feb | 1200 | |||
| 4 | Mar | #N/A | |||
| 5 | Apr | 800 | |||
| 6 | May | 900 | |||
| 7 | Jun | 600 | |||
| 8 |
2 Find the maximum score ignoring errors
Student scores include an error in cell D5. You need the highest valid score. =AGGREGATE(4,6,D2:D7) uses function_num 4 (MAX) and options 6 (ignore errors). Excel returns the largest number, 95, skipping the error.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Subject | Score | |||
| 2 | Alice | A | Math | 85 | 95 | ||
| 3 | Bob | A | Math | 92 | |||
| 4 | Cara | B | Math | 78 | |||
| 5 | Dan | B | Math | #N/A | |||
| 6 | Eve | A | Math | 95 | |||
| 7 | Finn | B | Math | 88 | |||
| 8 |
3 Count numbers in a range with errors
A product list has quantities, but one cell shows #VALUE!. You want to count how many numeric quantities exist. =AGGREGATE(2,6,E2:E7) uses function_num 2 (COUNT) and options 6 (ignore errors). Excel counts the five numbers and ignores the error, returning 5.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Quantity | |||
| 2 | Pen | Office | 1.5 | 100 | 10 | 5 | ||
| 3 | Notebook | Office | 3 | 50 | 5 | |||
| 4 | Eraser | Office | 0.5 | 200 | 20 | |||
| 5 | Marker | Office | 2 | 75 | #VALUE! | |||
| 6 | Folder | Office | 1 | 120 | 15 | |||
| 7 | Stapler | Office | 5 | 30 | 3 | |||
| 8 |
4 Get the second largest value while ignoring errors
A bonus list includes an error. You need the second highest bonus. =AGGREGATE(14,6,F2:F7,2) uses function_num 14 (LARGE), options 6 (ignore errors), and k=2. Excel ignores the error, sorts the numbers descending (500, 400, 300, 200, 100), and returns the second one: 400.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Department | Salary | Years | Rating | Bonus | |||
| 2 | Sam | Sales | 50000 | 5 | 4 | 500 | 400 | ||
| 3 | Tina | Sales | 60000 | 7 | 5 | 400 | |||
| 4 | Uma | HR | 45000 | 3 | 3 | 300 | |||
| 5 | Vic | IT | 70000 | 8 | 4 | #N/A | |||
| 6 | Wes | IT | 55000 | 4 | 5 | 200 | |||
| 7 | Xena | HR | 48000 | 2 | 3 | 100 | |||
| 8 |
Common AGGREGATE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The function_num or options argument is not a valid number (e.g., text or out of range). | Use a number between 1 and 19 for function_num and between 0 and 7 for options. Check for typos. |
| #NUM! | The k argument for functions like LARGE or SMALL is invalid (e.g., zero or larger than the count of values). | Ensure k is a positive integer no greater than the number of valid values in the range. |
| #DIV/0! | Using AVERAGE (function_num 1) on a range with no numeric values after ignoring errors. | Check that the range contains at least one number, or use IFERROR to handle the result. |
| #REF! | A referenced range is invalid or has been deleted. | Update the formula to point to existing cells or ranges. |
Tips for using AGGREGATE
- Use options 6 to ignore errors, 5 to ignore hidden rows, and 7 to ignore both.
- AGGREGATE can replace SUBTOTAL in most cases and adds error handling.
- For functions like LARGE or SMALL, provide the k argument after the range.
- Remember that function_num uses numbers, not names: 9 for SUM, 1 for AVERAGE, 4 for MAX.
- AGGREGATE ignores filtered-out rows only when you use options 5 or 7.
- You can use AGGREGATE with up to 253 ref arguments for multiple ranges.
Frequently asked questions
What is the difference between AGGREGATE and SUBTOTAL?
AGGREGATE offers more functions (19 vs 11) and can ignore error values, which SUBTOTAL cannot. Both can ignore hidden rows.
Can AGGREGATE ignore errors in a range?
Yes, use options 6 (ignore errors) or 7 (ignore errors and hidden rows).
Does AGGREGATE work with filtered data?
Yes, when you use options 5 or 7, it ignores rows hidden by a filter.
What does function_num 14 mean in AGGREGATE?
It means use the LARGE function, which returns the k-th largest value. You then supply k as the last argument.
Can AGGREGATE handle arrays?
Yes, AGGREGATE can accept arrays for ref1, but some functions may require array entry in older Excel versions.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. AGGREGATE reference