Using the AGGREGATE function to quickly summarize data in Excel

When calculating and summarizing data in Excel, sometimes you will encounter a case where a value in the data range is erroneous, causing the returned result to be erroneous as well. The AGGREGATE function is an aggregation function that can help you solve this problem and also comes with many other useful functions. Let’s learn more about the AGGREGATE function right now!

What is the AGGREGATE function?

The AGGREGATE function is an aggregation function that aggregates other functions in Excel and returns the aggregated result in a database. Specifically, the AGGREGATE function also allows users to ignore hidden or error rows or values.

Because it is an aggregation function of other functions, the AGGREGATE function can be used as a replacement for other functions such as AVERAGE, SUM, COUNT, MAX, MIN,… This is why the AGGREGATE function can help you save time when summarizing data in Excel.

How to use the AGGREGATE function in Excel

1. AGGREGATE function syntax

For reference forms:

=AGGREGATE(function_num, options, ref1, [ref2],…)

For array forms:

=AGGREGATE(function_num, options, array, [k])

Where:

• function_num (Required): Select a number from 1 to 19 for the desired function.
• options (Required): Select a number from 0 to 7 to set the conditions when using the function.
• ref1 (Required): The first parameter for functions with multiple parameters to be calculated.
• ref2 (Optional): The next parameter from 2 to 253.
• array (Required): The array to be evaluated.
• k: The corresponding parameter required for some functions.

2. function_num table in the AGGREGATE function

3. options table in the AGGREGATE function

Examples of the AGGREGATE function in Excel

To better understand how the AGGREGATE function works, let’s take a look at some specific examples!

We have a data table in Excel as follows: The data range is from B4:B14, in which rows B7, B9, B12 are error values.

Example 1: Calculate the sum of the values in the range B4:B14, ignoring error values.

Formula:

=AGGREGATE(9,6,B4:B14)

=> Result: 441.

Example 2: Calculate the sum of the values in the range B4:B14, without ignoring error values.

Formula:

=AGGREGATE(9,4,B4:B14)

=> Result: Since error values are not ignored, the result is an error.

Example 3: Find the maximum value in the range B4:B14, ignoring error values.

Formula:

=AGGREGATE(4,6, B4:B14)

=> Result: 102.

Example 4: Find the average value in the range B4:B14, ignoring error values.

Formula:

=AGGREGATE(1,6,B4:B14)

=> Result: 55.125.

Notes when using the AGGREGATE function

• The function_num table will be displayed immediately after typing AGGREGATE(.
• The options table will be displayed in full after selecting function_num and adding a comma “,”.
• If there are not enough arguments ref1 (or additional ref2 if necessary), the AGGREGATE function will return a #VALUE! error.
• The function does not work in a 3D reference.
• The AGGREGATE function is built to calculate vertically, not horizontally.

Conclusion

The AGGREGATE function is one of the most powerful functions in Excel because it can replace many other functions with the condition to exclude error values, helping to quickly return results to users. Hopefully through this article, you can understand how to use the AGGREGATE function to increase productivity when working with data in Excel. Currently, FPT Shop has a wide range of high-quality laptops that support the smoothest working process on your computer. Visit now!

Refer to the best-selling laptop products here: Cheap laptops

• How to Use Excel: The 5 Most Basic Functions for Beginners
• How to Use the COUNT Function in Excel: Simplest Guide Ever!

You may also like

5 Essential Excel Functions for Beginners

Master the fundamentals of Excel with these 5 basic functions! Learn how to use SUM, AVERAGE, COUNT, MIN, and MAX in order to make calculating and analyzing data easier and more efficient.

A Simple Method for Adding Watermark to Excel for All Spreadsheets in No Time

Watermarking in Excel is a great way to mark your ownership and protect the content on your Excel spreadsheets. If you’re unsure how to do it, you can follow the steps outlined in this article.

How to recover unsaved or overwritten Excel files effectively: a sure-fire method

Knowing how to recover unsaved Excel files can be extremely helpful in cases when your computer unexpectedly shuts down or you accidentally click on “Don’t save” when Excel asks if you want to save your changes before closing. Follow this article to learn the step-by-step process of how to accomplish it.

5+ Effective Ways to Convert PDF to Excel Quickly, Useful Office Tricks

Knowing the ways to convert PDF files to Excel below will enable you to easily edit and add content inside. Office workers and students definitely must know this trick if they want to simplify their tasks.