AGGREGATE function is the combination of 19 formulas. Using aggregate function is getting your work done at a fast pace.

Previously In Excel Formulas: How to use ADDRESS Function?

This function is used at times when you have to use a bunch of formulas and it takes forever to jot every formula down and bring out the value, which is why aggregate provides the solution to it.

Aggregate function upholds the following formulas’:

AGGREGATE Function
AGGREGATE Function
  • AVERAGE                                                                           
  • COUNT
  • COUNTA
  • MAX
  • MIN
  • PRODUCT
  • STDEV.S
  • STDEV.P
  • SUM                                                                                
  • VAR.S
  • VAR.P
  • MEDIAN
  • MODE.SNGL
  • LARGE
  • SMALL
  • PERCENTILE.INC
  • QUANRTILE.INC
  • PERCENTILE.EXE
  • QUARTILE.EXE

To understand the same, here’s an example below:

AGGREGATE Function

As you can observe in the image above, products of The Coca-Cola Company are mentioned under the “Product” Column and the sales per month of particular areas have been jotted down.

In the following example, we only have a listing of 14 products but in real, the data entries are difficult to count. 

At times, like this, there is a need of 2 to 3 formulas to organise the data and bring out information out of it. But as you can know that putting every formula one by one can cause a huge façade and confusion, so Excel has brought an excellent solution to it, the AGGREGATE Function!

Step1: Input the Formula

AGGREGATE Function

The formula for the following function is:

=AGGREGATE(

The moment you type the formula, you see a drop-down box giving you a choice between 19 different formulas. 

You can pick the formula that is as per your need. 

As you can see in the image, we have taken AVERAGE and SUM.

So, to calculate the average and sum of the product sales, here’s what we do:

STEP 2: Inputting the values in the Formula

After jotting down the formula, we will select option (…)1 – AVERAGE.

AGGREGATE Function

After putting the same, we can observe a drop-down box which asks you to pick an option for further details. This is the error section, at times your data entries have errors or there are empty cells, so to bring out your result while ignoring those errors, you can choose the option accordingly.

We will choose (…) 4 – Ignore nothing

AGGREGATE Function

After inserting coma, we have to select the cells i.e. the data from which we need our result to be calculated.

Then press “Enter”

AGGREGATE Function

There you go, the average value of the product sales is 579285.7

Same procedure is for SUM as well.

AGGREGATE Function

After putting the formula, choose the SUM option from the drop-down box.

AGGREGATE Function

As the following examples do not have any errors, so we will go with the option 4 again i.e. Ignore nothing.

AGGREGATE Function

After doing the same, put a coma and select the area (the cells from which you need to retrieve information).

And press enter.

AGGREGATE Function

The sum of product sales is 8110000.

You can use the aggregate function at times when you are puzzled up in a huge façade of data entries and calculations. It will make your work at ease.

AGGREGATE Function

It’s easy, isn’t it?