WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » AGGREGATE Excel Function

AGGREGATE Excel Function

AGGREGATE Function in Excel

AGGREGATE Function in excel returns the aggregate of a given data table or data lists, this function also has the first argument as function number and further arguments are for a range of the data sets, the function number should be remembered to know which function to use.

Syntax

There are two syntaxes for the AGGREGATE Formula:

  1. Reference Syntax

=AGGREGATE(function_num, options, ref1, ref2, ref[3],…)

  1. Array Syntax

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

Function_num is a number that denotes a specific function that we want to use; it is a number from 1-19

Aggregate Function (Function num)

Option: it is also a numeric value ranging from 0 to 7 and determines which values are to be ignored during calculations

Aggregate Function Option)

Ref1, ref2, ref[3]:  is the argument while using the reference syntax; it is numeric value or values on which we want to perform the computation, at least two arguments are required. Rest arguments are optional.

Array: is an array of values on which we want to operate; it is used in array syntax of the AGGREGATE function in excel.

K: is an optional argument and numeric value; it is used when the function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC in Excel is used.

Examples

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

Example – #1

Suppose we have a list of numbers, and we will compute the Average, Count that is the number of cells that contain a value, Counta -count of cells that are not empty, Maximum, Minimum, product, and sum of the given numeric values. Values are given below in the table:

Aggregate Excel Function Example 1

Aggregate Excel Function Example 1.1

Let us first calculate the Average in Row 9, for all given values. For average, the function_ num is 1

Aggregate Excel Function Example 1-2

In Column C, all values are given, and we won’t have to ignore any values so that we will select Option 4 (ignore nothing)

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Aggregate Excel Function Example 1-3

And selecting the range of values C1:C8 as an array of numeric values

Aggregate Excel Function Example 1-4

Since ‘k’ is an optional argument and is used when a function like LARGE, SMALL in Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC is used, but in this case, we are calculating the Average to omit the value of k.

So, the average value is

Aggregate Excel Function Example 1-5

Similarly, for range D1:D8, again, we will select option 4.

For range E1:E8, a cell E6 contains an error value. If we use the same AGGREGATE formula, we will get an error. Still, when an appropriate option is used, the AGGREGATE in Excel gives the average of the remaining values neglecting the error value in E6.

To ignore the error values, we have option 6.

Aggregate Example 1-6

Aggregate Example 1-7

Similarly, for range G1:G8, we will use the option 6 (ignore the error values)

Aggregate Example 1-8

Now, for range H3, if we put a value 64, and hide the third row and use option 5, to ignore the hidden row, the AGGREGATE in Excel, we will give the average value for visible numeric values only.

Output without Hiding Row 3

Aggregate Example 1-9

Output after Hiding Row 3

Aggregate Example 1-10

Applying the AGGREGATE formula for other operations, we have

Aggregate Example 1-11

Example – #2

Suppose we have a table for the revenue generated on different dates from the different channels as given below

Aggregate Excel Function Example 2

Now, we want to check the revenue generated for different channels. So, when we apply the sum function, we get the total revenue generated, but in case if we want to check the revenue generated for Organic channel or direct channel or any other, when we apply filters in excel for the same, the sum function will always give the total sum

Aggregate Excel Function Example 2-1

Aggregate Excel Function Example 2-2

We want that when we filter the channel, we get the sum of the visible values, so instead of using the SUM function, we will use the AGGREGATE function to get the sum of the values that are visible when a filter is applied.

So, replacing the SUM formula with an AGGREGATE function with option code 5 (ignoring the hidden rows and values), we have,

Aggregate Example 2-3

Now, when we apply the filter for different channels it will show the revenue for that channel only as the rest of the rows gets hidden.

Total Revenue Generated for Direct Channel:

Total Revenue Generated for Direct Channel

Total Revenue Generated for Organic Channel:

Total Revenue Generated for Organic Channel

Total Revenue Generated for Paid Channel:

Total Revenue Generated for Paid Channel

We can see the AGGREGATE  function calculates the different Sum values for the revenue generated for different channels once they are filtered. So, the AGGREGATE function can be dynamically used to replace different functions for different conditions without using the conditional formula.

Suppose for the same table, some of our revenue values contains an error, now we need to ignore the errors, and at the same time, if we want to apply a filter, the AGGREGATE function should ignore the hidden row values also.

Aggregate Example 2-7

When we use option 5, we get the error for the SUM of the total revenue. To ignore the errors, we have to use option 6

error for the SUM of the total revenue

Using option 6, we get the sum ignoring the error values. Still, when we apply the filter, for example, filter by channel value Direct, we get the same sum ignoring the errors, but at the same time, we have to ignore the hidden values also.

sum ignoring the error values

So, in this case, we will use option 7 that ignores the error values and, at the same time, the hidden rows.

option 7 that ignores the error values

Things to Remember

  • The AGGREGATE function doesn’t recognize the function _ num value greater than 19 or less than 1. Similarly, for Option number, it doesn’t identify the values greater than 7 and less than 1; if we provide any other values, it gives a #VALUE! Error
  • It always accepts the numeric value and always returns a numeric value as an output
  • The AGGREGATE in Excel has a limitation; it only ignores the hidden rows but does not ignore the hidden columns.

AGGREGATE Excel Function Video

Recommended Articles

This has been a guide to the AGGREGATE Function in Excel. Here we discuss the AGGREGATE Formula in excel and how to use AGGREGATE in Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • REPLACE Function in Excel
  • POWER Function in Excel
  • IF Function
  • True Function in Excel
4 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download AGGREGATE Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More