Pro Templates

Excel AGGREGATE function

Summary

The Excel AGGREGATE function is a versatile tool for performing a variety of calculations like SUM, AVERAGE, COUNT, MAX, and more, with options to ignore errors, hidden rows, or nested subtotals. It is especially useful in complex datasets where certain elements need to be excluded from calculations.
Syntax
				
					=AGGREGATE(function_num, options, ref1, [ref2], ...)
  • function_num: Specifies the function to use (e.g., 1 for AVERAGE, 9 for SUM)
  • options: Specifies which values to ignore in the operation
  • ref1, [ref2], …: The range or ranges on which to perform the calculation
Return value

The result of the chosen aggregate function, depending on the specified options.

How to use

Select the desired function using function_num, set options to control how to handle errors, hidden rows, or nested subtotals, and then specify the range(s) for the calculation.

function_num values:

  • 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

 

options values:

  • 0: Ignore nested SUBTOTAL and AGGREGATE functions
  • 1: Ignore hidden rows, nested SUBTOTAL and AGGREGATE
  • 2: Ignore error values, nested SUBTOTAL and AGGREGATE
  • 3: Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE
  • 4: Ignore nothing
  • 5: Ignore hidden rows
  • 6: Ignore error values
  • 7: Ignore hidden rows and error values

 

Examples

Simple AGGREGATE
Summing Values while Ignoring Errors: To sum a range while ignoring error values:
				
					=AGGREGATE(9, 6, A2:A10)
This formula sums the values in A2:A10, excluding any errors found within the range.
AGGREGATE for Maximum Value
Finding the Maximum Value Excluding Hidden Rows: To determine the maximum value in a range, ignoring hidden rows:
				
					=AGGREGATE(4, 5, A2:A10)
This computes the maximum value in A2:A10 while ignoring any values in hidden rows.
AGGREGATE with COUNTA Function
Counting Non-Empty Cells, Ignoring Errors: To count non-empty cells in a range, excluding error values:
				
					=AGGREGATE(3, 6, A2:A10)
Counts the number of non-empty cells in A2:A10, ignoring cells with errors.
AGGREGATE with AVERAGE Function
Calculating Average, Excluding Zeros and Errors: To calculate the average of a range, excluding zeros and errors:
				
					=AGGREGATE(1, 6, A2:A10)
This formula averages the values in A2:A10, ignoring both zero values and errors.
AGGREGATE in Financial Calculations
Summing Financial Data, Ignoring Subtotals and Hidden Rows: To sum financial data while ignoring subtotals and hidden rows:
				
					=AGGREGATE(9, 7, A2:A10)
This sums the values in A2:A10, but excludes any hidden rows and subtotals.

Additional Notes

  • AGGREGATE is a powerful function, ideal for handling data inconsistencies and complex calculations.
  • It provides a wide range of calculations, enhancing versatility in data analysis.
  • This function is particularly useful in large and complex spreadsheets where standard functions might not suffice due to data complexities.

Related Functions

Excel SUBTOTAL function

The Excel SUBTOTAL function performs calculations like SUM, AVERAGE on filtered lists, ideal for dynamic data analysis.

Excel MIN function

The Excel MIN function identifies the lowest value in a data set, crucial for determining minimums in analyses.

Excel AVERAGE function

The Excel AVERAGE function calculates the mean of numbers, essential for summarizing central tendencies in data sets.

Excel SUM function

The Excel SUM function adds together numbers, fundamental for financial totals and aggregating data.

Excel MAX function

The Excel MAX function finds the highest value in a set, key for identifying peak data points.

Excel COUNT function

The Excel COUNT function counts cells containing numbers, fundamental for quantifying data entries in a range.

Content Navigation