**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