AGGREGATE function in Excel

In this post, you’ll learn about the Aggregate function in Microsoft Excel how this can be used for calculations and in formulas in your Excel worksheet.

Aggregate usually means the whole sum of data and the function also means the same.

Aggregate function

Aggregate function returns aggregate of functions.

It returns the aggregate of range of values of whatever function number is mentioned in the Aggregate function. There are various options in which the Aggregate function can process the provided values.

Syntax – AGGREGATE(function_num,options,ref1,ref2)

It works for a total number of 19 functions. The numbering of the functions are:

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.EXE

19 – QUARTILE.EXC

The numbering of options are:

0 – SUBTOTAL and AGGREGATE functions

1 – Hidden rows, SUBTOTAL and AGGREGATE functions.

2 – Error values, SUBTOTAL and AGGREGATE functions.

3 – Hidden rows, Error values, SUBTOTAL and AGGREGATE functions.

4 -Nothing

5 – Hidden Rows

6 – Error values

7 – Hidden rows and Error values

Now let’s take a look at an example.

AGGREGATE function in Excel

Here we’ve used the SUM function and included everything between the cell ranges C3 to C8.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.