AGGREGATE Excel Function - Formula, Examples, How to Use?

What Is AGGREGATE Excel Function?

The AGGREGATE Excel function returns the result of the specified aggregate operation performed on columns of data or the vertical range. Users can apply Excel functions such as SUM, AVERAGE, MAX, and COUNT, using the AGGREGATE() function. It also provides the choice to ignore error values and hidden rows.

The AGGREGATE function in Excel is an inbuilt function which means we can enter the formula directly in the worksheet or insert it from the “Math & Trig” option from the “Function Library”.

📥Download the ready-to-use Excel template to practice this tutorial yourself.

Download Excel Template

For example, the first table below shows a student’s scores in eight subjects.

Frequently Asked Questions (FAQs)

How to use the SUM AGGREGATE Excel function?

We can use the SUM AGGREGATE Excel function to determine the total units ordered.

The following table shows our source data, a list of gadgets, and ordered units with few error values.

The steps to apply the SUM AGGREGATE function are:

Step 1: Select cell B13 and enter as indicated. First, enter =AGGREGATE(, double-click number 9 (SUM).

Step 2: Then enter a ‘,’ and double-click 6 (Ignore error values) to enter the options argument.

FAQ 1.2

Step 3: Then enter a ‘,’ and select the array range to add the data points. Close the brackets.

The complete formula is =AGGREGATE(9,6,B2:B11).

FAQ 1.3

Step 4: Press the “Enter” key.

FAQ 1.4

The output is 1890, as shown above i.e., the sum value, ignoring the error values.

Which values does the AGGREGATE Excel function ignore?

The AGGREGATE Excel function ignores:

1. Hidden rows.
2. Error-values.
3. Nested SUBTOTAL() and AGGREGATE().

When to use the AGGREGATE Excel function?

We can use the AGGREGATE Excel function to execute a specific aggregate operation, such as SUM or MEDIAN, for a data column or a vertical range.

Download Template