EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel AGGREGATE Function in Excel
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

AGGREGATE Function in Excel

By Pradeep SPradeep S

AGGREGATE in Excel

AGGREGATE in Excel (Table of Contents)

  • AGGREGATE in Excel
  • AGGREGATE Formula in Excel
  • How to Use AGGREGATE Function?

AGGREGATE in Excel

Aggregate function comprises multiple mathematical functions such as average, sum, max, min, mean, etc., along with the conditions which are customized for each of these sub-functions available under aggregate function. We can even ignore the different cells or rows such as a blank row, error value, etc. and get the output as we want. We have a total of 19sub-functions available in the Aggregate function with different Options.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

Definition:

“Returns the AGGREGATE in a database of values or list”

It means it performs a number of calculations (19 Excel statistical functions)

AGGREGATE Formula in Excel

An AGGREGATE function has two types of formula

It can be noticed when you type the AGGREGATE function in an excel cell

AGGREGATE Formula

1) AGGREGATE in Reference form

AGGREGATE Reference Formula

2) AGGREGATE in Array Formula

AGGREGATE Array Formula

Function_num: It is a number, it can be from 1 to 19, It depends on which specific function you want to use in the below-mentioned list

Here each number represents a function; it is a compulsory argument

1 to 13 are Reference form, and 14 to 19 are array form

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: is the number 0 to 7 that specifies which values to ignore for the aggregate function Note: If the options parameter is omitted, by default, options is set to 0

0: Ignore nested SUBTOTAL and AGGREGATE functions

1: Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows

2: Ignore nested SUBTOTAL, AGGREGATE functions, and error values

3: Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows & error values

4: Ignore nothing

5: Ignore hidden rows

6: Ignore error values

7: Ignore hidden rows and error values

ref1, ref2, ref[3]: It is the first numeric argument for the function when using the REFERENCE syntax. it is values or numeric value on which we want to perform the computation, Here minimum of two arguments are required, the rest arguments are optional; for the second reference, Numeric arguments should be between 2 to 253 for which you want the aggregate value

Array: An array or refers to a range of cells when using the ARRAY syntax

[k]: The last 6 functions (under 1 to 19 function list): k value as a fourth argument

It is an optional argument; it is used if we need to find out LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, or QUARTILE.EXC when using the ARRAY syntax

How to Use the AGGREGATE Function in Excel?

This AGGREGATE function is very simple easy to use. Let us now see how to use the AGGREGATE function with the help of some examples.

You can download this AGGREGATE Function Excel Template here – AGGREGATE Function Excel Template

Example #1

The following table contains yearly sales data (2015)

AGGREGATE Example 1-1

If you run the =SUM (B8:B16) function directly in cell B17,

AGGREGATE Example 1-2

It gives the correct value because that column does not contain hidden rows, errors & nested subtotals

AGGREGATE Example 1-3

Here will calculate the SUM using an AGGREGATE function in cell B19.

=AGGREGATE(9,4,B8:B16),

AGGREGATE Example 1-4

The result will be 487.

AGGREGATE Example 1-5

  • Function_ num: For SUM function, the function_ num is 9
  • Option: In Column B, i.e. For 2015-year sales data, all values are given and we won’t have to ignore any values; it does not contain hidden rows, errors & nested subtotals. so, we will select Option 4 (ignore nothing)
  • Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is B8:B16, it is selected as an array of numeric values
  • ‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. We are calculating the SUM here so that we will omit the value of k. 

Example #2

The following table contains yearly sales data (2016)

AGGREGATE Example 2-1

In column C, for the 2016 yearly sales data. In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A); in the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the error value.

If you run the =SUM (C8:C16) function directly in cell C17,

AGGREGATE Example 2-2

It returns an error value due to an error in that range. because that column contains #DIV/0! & #N/A errors.

AGGREGATE Example 2-3

In order to ignore the error values, we have to use option 6 in an AGGREGATE function

=AGGREGATE(9,6,C8:C16).

AGGREGATE Example 2-4

The result or output will be 334.

AGGREGATE Example 2-5

  • Function_ num: For SUM function, the function_ num is 9
  • Option: In Column C, i.e. For 2016-year sales data, In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A). To ignore these errors, we will select Option 6 (Ignore error values)
  • Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is C8:C16, it is selected as an array of numeric values
  • ‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. We are calculating the SUM here so that we will omit the value of k.

when an appropriate option is used in the AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cells C11 & C12. i.e. 334

Example #3

The following table contains yearly sales data (2017)

AGGREGATE Example 3-1

In column D, for the 2017 yearly sales data. In the range D8:D16, a cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A), In the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the hidden row & error value.

If you run the =SUM (D8:D16) function directly in cell D17,

AGGREGATE Example 3-2

It returns an #N/A error value due to an error in that range. because that column contains #N/A error & hidden row or blank value.

AGGREGATE Example 3-3

=AGGREGATE(9,7,D8:D16),

AGGREGATE Example 3-4

The result or output will be 262

AGGREGATE Example 3-5

  • Function_ num: For SUM function, the function_ num is 9
  • Option: In column D, for the 2017 yearly sales data. In the range D8:D16, a cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A). To ignore these errors, we will select Option 7 (Ignore hidden rows and error values)
Note: you can hide rows by selecting the row(s) > right-click on specific row > Hide
  • Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is D8:D16, it is selected as an array of numeric values
  • ‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. We are calculating the SUM here so that we will omit the value of k.

when an appropriate option is used in an AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cell D9 & D12. i.e. 262

Things to Remember

  • The AGGREGATE function is applicable only for vertical ranges or columns of data. It is not designed for horizontal range or rows of data.
  • It has a limitation; it only ignores the hidden rows; it does not ignore the hidden columns.
  • An AGGREGATE function is applicable only for the numeric value
  • Function _ num argument value should not be greater than 19 or less than 1, similarly for option argument should not be greater than 7; otherwise, it will give #VALUE! error
  • In AGGREGATE function, if in function number argument, if you are using 14 to 19 (LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC), the “K” argument should be used. g. =AGGREGATE(15, 6, A1:A9, 3). If the “K” value or second reference argument is ignored, it will result in a #VALUE! error

Recommended Articles

This has been a guide to AGGREGATE Function. Here we discuss the AGGREGATE Formula and how to use the AGGREGATE Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. Excel Formula of Percentage
  2. Logical Functions in Excel
  3. Excel Percentage Difference
  4. Excel SUMIF with OR
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
6 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download AGGREGATE Function Excel Template

EDUCBA

Download AGGREGATE Function Excel Template

EDUCBA

डाउनलोड AGGREGATE Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more