Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Count rows with at least n matching values
  • How to get relative column numbers in a range in Excel
  • How to use Excel COLUMN Function
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to calculate current stock or inventory in Excel
  • How to Create Area Chart in Excel
  • How To Create Pareto Chart in Excel
  • How to calculate average last N values in a table in Excel
  • Everything about Charts in Excel

Data Validation Examples

  • Excel Data validation with conditional list
  • Excel Data validation allow uppercase only
  • Excel Data validation unique values only
  • Excel Data validation must not contain
  • Excel Data validation only dates between

AGGREGATE function: Description, Usage, Syntax, Examples and Explanation

by

What is AGGREGATE function in Excel?

AGGREGATE function is one of the Math and Trig functions in Microsoft Excel that returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

Syntax of AGGREGATE function

Reference form

AGGREGATE(function_num, options, ref1, [ref2], …)

Array form

AGGREGATE(function_num, options, array, [k])

Example of AGGREGATE function

Steps to follow:

1. Open a new Excel worksheet.

2. Copy data in the following table below and paste it in cell A1

Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.

You can adjust the column widths to see all the data, if need be.

#DIV/0! 82
72 65
30 95
#NUM! 63
31 53
96 71
32 55
81 83
33 100
53 91
34 89
Formula Description Result
=AGGREGATE(4, 6, A1:A11) Calculates the maximum value while ignoring error values in the range 96
=AGGREGATE(14, 6, A1:A11, 3) Calculates the 3rd largest value while ignoring error values in the range 72
=AGGREGATE(15, 6, A1:A11) Will return #VALUE! error. This is because AGGREGATE is expecting a second ref argument, since the function (SMALL) requires one. #VALUE!
=AGGREGATE(12, 6, A1:A11, B1:B11) Calculates the median while ignoring error values in the range 68
=MAX(A1:A2) Will return error value, since there are error values in the evaluation range. #DIV/0!
Worked Example:   How to Sum Range with Errors in Excel
The AGGREGATE function syntax has the following arguments:

  • Function_num: A number 1 to 19 that specifies which function to use.
Function_num Function
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: 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)

Option 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 functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error values
  • Ref1:The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.
  • Ref2,…    ( 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:
Function
LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)

AGGREGATE formula explanation

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.

 

Post navigation

Previous Post:

ACOTH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

TRUNC function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • CODE function: Description, Usage, Syntax, Examples and Explanation
  • How to check if cell contains all of many things in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • Split dimensions into three parts in Excel
  • Get work hours between dates custom schedule in Excel
  • How to calculate nth day of week in month in Excel
  • Basic Overtime Calculation Formula in Excel
  • How to get workdays between dates in Excel
  • List holidays between two dates in Excel
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • Calculate cumulative loan principal payments in Excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate present value of annuity in excel
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles