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.
Here we’ve used the SUM function and included everything between the cell ranges C3 to C8.
