What's the difference between AGGREGATE(9,...) and SUM()?

SUM ignores text and logical values but fails on errors (#DIV/0!, #N/A); AGGREGATE can be configured to skip errors entirely (options 2, 3, 6, or 7). AGGREGATE also optionally ignores hidden rows and nested SUBTOTAL functions, making it safer for filtered or complex data.

When should I use AGGREGATE instead of SUBTOTAL?

Use SUBTOTAL for traditional subtotaled reports (it automatically excludes other SUBTOTAL results). Use AGGREGATE when your data contains errors or hidden rows that you want to skip, or when you need both error-handling and hidden-row filtering in one formula.

How do I ignore both hidden rows and errors at the same time?

Set options=7. This combines all three filters: bit 1 (ignore hidden rows) + bit 2 (ignore errors) + bit 4 (ignore SUBTOTAL results). For example, =AGGREGATE(9,7,A1:A100) sums A1:A100 while skipping hidden rows, error values, and nested SUBTOTALs.

Can AGGREGATE handle multiple non-adjacent ranges?

Not with a single ref argument. AGGREGATE requires contiguous ranges per ref. To sum multiple non-adjacent columns, nest multiple AGGREGATE calls in a SUM, or use multiple ref arguments if your data structure allows (e.g., =AGGREGATE(9,6,A:A,C:C) on some spreadsheets).

AGGREGATE function

Returns the result of a function (like SUM, AVERAGE, COUNT) applied to a range, with built-in options to ignore error values, hidden rows, and nested SUBTOTALs.

=AGGREGATE(function_num, options, ref1, ...)

Generate a AGGREGATE formula

Describe what you need. The generator will reach for AGGREGATE where AGGREGATE is the right tool, and tell you when it is not.

Arguments

How AGGREGATE reads its arguments

Argument	Required	Description
function_num	Required	Integer from 1 to 19 specifying which function to apply: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=CNTBLANK, 5=MAX, 6=MIN, 9=SUM, 11=VAR.S, 12=VAR.P, 13=STDEV.S, 14=STDEV.P, and others.
options	Required	Integer from 0 to 7 controlling filtering: 0–3 skip hidden rows (bit 1), errors (bit 2), or both; 4–7 also skip nested SUBTOTAL results. Use 6 to ignore errors and SUBTOTAL; use 5 to ignore hidden rows and SUBTOTAL.
ref1	Required	Required range of cells to apply the function to; must be contiguous or a single range reference.
...	Repeating	Optional additional ranges; if provided, AGGREGATE treats them as concatenated for the calculation (e.g., ref1 and ref2 combined for the function).

Returns

A number representing the result of the function specified by function_num (sum, average, count, etc.).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count active subscription customers

Customer	Plan	MRR	Signup Date	Status
Alice	Pro	99	1/1/2024	Active
Bob	Basic	29	2/1/2024	Churned
Carol	Pro	99	3/1/2024	Active
David	Pro	99	4/1/2024	Churned
Eve	Basic	29	5/1/2024	Active
Frank	Pro	#REF!	6/1/2024	Inactive
Grace	Basic	29	7/1/2024	Active

=AGGREGATE(3, 5, A2:A8)

Result: 7

AGGREGATE with function_num=3 (COUNTA) counts non-empty cells in the Customer column. The options=5 ignores hidden rows and nested SUBTOTAL functions. All 7 customer names are counted, including Frank, because the error is in the MRR column, not the Customer column.

2. Sum total monthly recurring revenue, skipping errors

Customer	Plan	MRR	Signup Date	Status
Alice	Pro	99	1/1/2024	Active
Bob	Basic	29	2/1/2024	Churned
Carol	Pro	99	3/1/2024	Active
David	Pro	99	4/1/2024	Churned
Eve	Basic	29	5/1/2024	Active
Frank	Pro	#REF!	6/1/2024	Inactive
Grace	Basic	29	7/1/2024	Active

=AGGREGATE(9, 6, C2:C8)

Result: 384

AGGREGATE with function_num=9 (SUM) adds values in the MRR column. The options=6 ignores error values and SUBTOTAL functions. Frank's #REF! error is skipped, leaving 99+29+99+99+29+29=384 as the total MRR across all customers.

3. Calculate average monthly revenue per customer

Customer	Plan	MRR	Signup Date	Status
Alice	Pro	99	1/1/2024	Active
Bob	Basic	29	2/1/2024	Churned
Carol	Pro	99	3/1/2024	Active
David	Pro	99	4/1/2024	Churned
Eve	Basic	29	5/1/2024	Active
Frank	Pro	#REF!	6/1/2024	Inactive
Grace	Basic	29	7/1/2024	Active

=AGGREGATE(1, 6, C2:C8)

Result: 64

AGGREGATE with function_num=1 (AVERAGE) calculates the mean of the MRR column. The options=6 skips the error in Frank's row. The average of the six valid values (99, 29, 99, 99, 29, 29) is 384÷6=64 per customer.

Common errors

Which AGGREGATE error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The function_num argument is outside the valid range 1–19, or specifies a function code that does not exist.	Verify that function_num is an integer from 1 to 19; check a reference table for the correct code (e.g., 9 for SUM, 1 for AVERAGE).
#REF!	One of the range references (ref1, ref2, etc.) points to a cell or range that has been deleted, moved, or is otherwise invalid.	Re-select the ranges in the formula, or edit the sheet to restore the deleted cells; then recalculate.
#VALUE!	The options argument is not a whole number between 0 and 7, or function_num is not a valid integer.	Ensure function_num is an integer 1–19 and options is a whole number 0–7; for example, use 6 (not 6.5) for 'ignore errors and SUBTOTAL'.

Tips and when to use something else

Use options=6 to automatically skip error values (#DIV/0!, #N/A, #REF!) without manual error-handling functions—much cleaner than nested IFERROR.
Set options=5 or higher to exclude filtered and hidden rows; options=0–3 include them. This is useful when users filter a table and you want calculations to update automatically.
For conditional sums (e.g., 'sum MRR only for Pro plans'), use SUMIF or SUMIFS instead—AGGREGATE does not support conditions.
AGGREGATE respects options 6–7, which skip nested SUBTOTAL results; use this to avoid double-counting in reports that already have subtotals.

Related functions

SUMIFSUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.SUBTOTALReturns a subtotal of numeric values in a range, optionally excluding hidden rows and nested SUBTOTAL functions.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.

Frequently asked questions

What's the difference between AGGREGATE(9,...) and SUM()?: SUM ignores text and logical values but fails on errors (#DIV/0!, #N/A); AGGREGATE can be configured to skip errors entirely (options 2, 3, 6, or 7). AGGREGATE also optionally ignores hidden rows and nested SUBTOTAL functions, making it safer for filtered or complex data.
When should I use AGGREGATE instead of SUBTOTAL?: Use SUBTOTAL for traditional subtotaled reports (it automatically excludes other SUBTOTAL results). Use AGGREGATE when your data contains errors or hidden rows that you want to skip, or when you need both error-handling and hidden-row filtering in one formula.
How do I ignore both hidden rows and errors at the same time?: Set options=7. This combines all three filters: bit 1 (ignore hidden rows) + bit 2 (ignore errors) + bit 4 (ignore SUBTOTAL results). For example, =AGGREGATE(9,7,A1:A100) sums A1:A100 while skipping hidden rows, error values, and nested SUBTOTALs.
Can AGGREGATE handle multiple non-adjacent ranges?: Not with a single ref argument. AGGREGATE requires contiguous ranges per ref. To sum multiple non-adjacent columns, nest multiple AGGREGATE calls in a SUM, or use multiple ref arguments if your data structure allows (e.g., =AGGREGATE(9,6,A:A,C:C) on some spreadsheets).

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17