Technology

Using the AGGREGATE function to quickly summarize data in Excel

The AGGREGATE function is a complex and less commonly used Excel function for many users. However, it is a tool that can save you a lot of time working in Excel if you understand it well. Have you ever used the AGGREGATE function before?

Administrator

14 December, 2023

145

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

function_num	Function
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.EXC
19	QUARTILE.EXC

3. options table in the AGGREGATE function

options	Condition
0	Ignore rows with SUBTOTAL and AGGREGATE functions
1	Ignore hidden rows and SUBTOTAL and AGGREGATE functions
2	Ignore error values, nested SUBTOTAL and AGGREGATE functions
3	Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4	Do not ignore anything
5	Ignore hidden rows
6	Ignore error values
7	Ignore hidden rows and error values

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