AGGREGATE function - Zoho Sheet - Help Guide

AGGREGATE

Allows execution of functions from the given list, and selectively ignores the hidden rows and errors in the range.

 

Syntax

 

AGGREGATE(function; option; range1; [range2])

function: A number specifying the function to perform. Ranges from 1 to 19. Eg., 1

 

Number

Function

1

AVERAGE

2

COUNT

3

COUNTA

4

MAX

5

MIN

6

PRODUCT

7

STDEV.S

8

STDEV.P

9

SUM

10

VAR

11

VARP

12

MEDIAN

13

MODE

14

LARGE

15

SMALL

16

PERCENTILE.INC

17

QUARTILE.INC

18

PERCENTILE.EXC

19

QUARTILE.EXC

 

option: A number that specifies the values to be ignored from the given range. Ranges from 0 to 7. Eg., 6 

Number

Behavior

0

Ignore nested SUBTOTAL and AGGREGATE functions

1

Ignore hidden rows, nested 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

Ignore nothing

5

Ignore hidden rows

6

Ignore error values

7

Ignore hidden rows and error values.


range: The numerical arguments for the function. Eg., A2:A9 

Examples

 

Formula

Result

=AGGREGATE(1;6;A2:A9;B2:B9)

36.066666667

=AGGREGATE(9;6;A2:A9)

214


Get a hands-on experience of the function in the embedded range below.   


 

Possible Errors

 

Errors

Meaning

#N/A!

  • The function could not find the value it was asked to look for. Commonly occurs in VLOOKUP and similar functions.

#NAME!

  • The function name is incorrect or invalid.

  • The given defined name (if any) is invalid.

  • There is a typo in the defined name used in the function.

  • Double quotes are missing for text values in the function.

  • Colon missing in a cell range reference.

#VALUE!

  • The given argument's data type is incorrect or invalid.
    For example, the function =DATE(A1:A3) will return a #VALUE error when a text value is present in the range.

  • The second numerical argument is missing when required.

#REF!

  • The given cell reference is incorrect or invalid. This error may occur when a cell/range has been deleted, moved. or pasted over.

 

Similar Functions