AGGREGATE

Database Functions

(4.8/5)

Returns an aggregate in a list or database by choosing from 19 available aggregation functions. Ignores hidden rows, error values, and SUBTOTAL results.

Interactive Formula Tester

Parameters:

Formula:

=AGGREGATE("1, 6, 10,20,30,40,50")

Complete Theory & Understanding

Master the fundamentals of Excel AGGREGATE function

Core Concept

The AGGREGATE function provides a flexible way to perform 19 different types of aggregation (AVERAGE, COUNT, MAX, MIN, PERCENTILE, etc.) while automatically handling error values, hidden rows, and nested SUBTOTAL/AGGREGATE functions. This makes it more robust than standard aggregation functions, especially when dealing with dirty data or filtered lists.

Why Use AGGREGATE?

Calculate statistics on visible rows in filtered data
Perform aggregations on data containing error values
Create flexible reports with nested SUBTOTAL functions
Calculate percentiles, quartiles, and rank statistics

Key Characteristics

19 Aggregation Options

Supports AVERAGE, COUNT, COUNTA, MAX, MIN, LARGE, SMALL, PERCENTILE, QUARTILE, STDDEV, VAR, and more

AGGREGATE(1,0,range) = AVERAGE

Smart Error Handling

Automatically ignores #N/A, #VALUE!, #DIV/0!, #REF!, #NUM!, #NAME?, or #NULL! errors

AGGREGATE(1,6,range) ignores all errors

Hidden Row Control

Optional capability to include or exclude hidden rows from calculations

AGGREGATE(1,1,range) includes hidden rows

Nested Subtotal Protection

Can ignore results from nested SUBTOTAL/AGGREGATE functions to prevent double-counting

AGGREGATE(1,0,range) ignores nested aggregates

Function Anatomy

=AGGREGATE(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Filtered Lists Analysis

Calculate statistics on visible rows in filtered data

Error-Proof Calculations

Perform aggregations on data containing error values

Dynamic Reporting

Create flexible reports with nested SUBTOTAL functions

Advanced Statistics

Calculate percentiles, quartiles, and rank statistics

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=AGGREGATE(function_num, options, array, k)

Required

function_num:

A number 1-19 that specifies which aggregation function to use (1=AVERAGE, 2=COUNT, 3=COUNTA, etc.)

Required

options:

A number 0-7 that specifies which values to ignore (0 or omitted: ignore nested SUBTOTAL/AGGREGATE, 1: ignore hidden rows, 2: ignore error values, etc.)

Required

array:

An array, array formula, or reference to cells for aggregation

Optional

A second argument required for SMALL, LARGE, PERCENTILE, or QUARTILE functions

Returns

Return Value:

Result from the specified aggregation function

Description: Aggregates data while ignoring errors, hidden rows, and nested subtotals

Interactive Examples

Average Ignoring Errors

Calculate average while ignoring #DIV/0! errors

Input Text:

"Values: {10, 20, #DIV/0!, 30, 40}"

Formula:

=AGGREGATE(1, 6, A1:A5)

Result:

Uses AVERAGE (1), ignores errors (6), returns 25

VBA Implementation & Automation

Basic AGGREGATE in VBA

Simple VBA implementation

Sub UseAggregate()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Example 1: Average ignoring errors (function 1, option 6)
    ws.Range("C1").Value = Application.WorksheetFunction.Aggregate(1, 6, ws.Range("A1:A10"))
    
    ' Example 2: Count visible rows (function 3, option 5)
    ws.Range("C2").Value = Application.WorksheetFunction.Aggregate(3, 5, ws.Range("A1:A20"))
    
    ' Example 3: Find third largest value (function 14, k=3)
    ws.Range("C3").Value = Application.WorksheetFunction.Aggregate(14, 0, ws.Range("A1:A20"), 3)
End Sub