What is the Excel AGGREGATE Function?
The Excel AGGREGATE function returns an aggregate calculation from 19 available function with eight available options.
AGGREGATE Syntax
AGGREGATE(function_num, options, ref1, ref2)
function_num, required, a function to use (1-19).
options, required, which values to ignore (0-7)
ref1, required, the first numeric argument
ref1, optional, the second numeric argument (mandatory for function with k argument)
The Function to Use
The table below is a number 1-19 following the function name to use for function_num argument
Which Values to Ignore
Unlike the SUBTOTAL function which only has “Ignore/Include hidden value”, the AGGREGATE function has more options to ignore. There are eight choices for “which values to ignore”
How to Use AGGREGATE Function in Excel
SUM Only Visible Cells
For example, there is data as shown below. Columns A, B and C contain the same data; a different function will calculate data in each column.
The formula
The formula for column A
=SUM(A2:A6)
The formula for column B
=SUBTOTAL(109,B2:B6)
function_num=109, SUM function and ignores hidden values.
The formula for column C
=AGGREGATE(9,7,C2:C6)
Function_sum=9, SUM function
Options=7, Ignore hidden rows and error values
What are the results of the three functions if row 4 and 5 is hidden using the “Hide Rows” menu (/, H, O, U, R)
The SUM function displays the same number and calculates the hidden values. The SUBTOTAL function and the AGGREGATE function ignore hidden values and return the different result from the SUM function.
Which one is better for “SUM Only Visible Cells”? It’s up to you, both return the same result, but the AGGREGATE function has more function than the SUBTOTAL function.
SUM with Error Value
With the same data and formula as the previous example.
What is the result if line 1 is changed to a divide by zero error?
Only the AGGREGATE function can calculate data with errors. No other function has the “Ignore error values” option
There is another way to calculate data with errors. First by modifying the formula to prevent error or second using an array formula. For a more detailed explanation, read the article below
