Home › Functions › Math & Trig › =AGGREGATE()
Math & Trig Excel 2010 ◉ 37 views

=AGGREGATE()

The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016Excel Web App
=AGGREGATE(function_num, options, ref1, [ref2], …)
Hover argument names in the table for details.
Output typeNumber
Popularity85 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =AGGREGATE() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
Download AGGREGATE.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
function_num Any type Required A number 1 to 19 that specifies which function to use. 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.
options Range Required A numerical value that determines which values to ignore in the evaluation range for the function. Note The function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, for example: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1) 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 functions; 4 = Ignore nothing; 5 = Ignore hidden rows; 6 = Ignore error values; 7 = Ignore hidden rows and error values.
ref1 Number Required The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.
ref2 Range Required ,... Optional. Numeric arguments 2 to 253 for which you want the aggregate value. For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:

▣ Examples

▦ Data
CellValue
A2 #DIV/0!
B2 82
A3 72
B3 65
A4 30
B4 95
A5 #NUM!
B5 63
A6 31
B6 53
A7 96
B7 71
A8 32
B8 55
A9 81
B9 83
A10 33
B10 100
A11 53
B11 91
A12 34
B12 89
A13 #DIV/0!
B13 82
A14 72
B14 65
A15 30
B15 95
A16 #NUM!
B16 63
A17 31
B17 53
A18 96
B18 71
A19 32
B19 55
A20 81
B20 83
A21 33
B21 100
A22 53
B22 91
A23 34
B23 89
Calculates the maximum value while ignoring error values in the range =AGGREGATE(4, 6, A1:A11)
Result: 96
Calculates the 3rd largest value while ignoring error values in the range =AGGREGATE(14, 6, A1:A11, 3)
Result: 72
Will return #VALUE! error. This is because AGGREGATE is expecting a second ref argument, since the function (SMALL) requires one. =AGGREGATE(15, 6, A1:A11)
Result: #VALUE!
Calculates the median while ignoring error values in the range =AGGREGATE(12, 6, A1:A11, B1:B11)
Result: 68
Will return error value, since there are error values in the evaluation range. =MAX(A1:A2)
Result: #DIV/0!

§ Remarks

  • Function_num⁠:⁠
  • As soon as you type the function_num argument when you enter the AGGREGATE function into a cell on the worksheet, you will see a list of all functions that you can use as arguments.
  • Errors:
  • If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error.
  • If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value.
  • Type of Range:
  • The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.

✦ Tips

  • Function_num⁠:⁠
  • As soon as you type the function_num argument when you enter the AGGREGATE function into a cell on the worksheet, you will see a list of all functions that you can use as arguments.
  • Errors:
  • Type of Range:
  • The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.

⚠ Common Errors

#VALUE! If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value.
Fix: Check the first two arguments and the argument count

⇄ Related Functions

=IFERROR() How to use the IFERROR function in Excel to trap and handle errors in a formula. =SUBTOTAL() Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function. =SUM() How to use the SUM function in Excel to add individual values, cell references, ranges, or a mix of all three.

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.