[{"id":"53ae7f99-150b-4b50-9f72-02f7f2ea5efe","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e371599e-67ae-4635-802b-0ada531e5b5b","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f309ec90-559b-42bf-955b-492431019196","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"28d36d47-c582-4210-a848-5ba48491bb80","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"9d13df60-758a-4cc3-9cda-8d6234e00821","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2e473c2c-8b35-40de-bdce-f21478f434bc","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e19a99e0-5b6f-4c8b-b872-47b3e78011bf","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2f718a98-6d54-4dc1-89c3-6b87cfabd16b","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"df066912-4ce1-4484-b00c-86b34a6e977b","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1fcab497-8c1f-413e-bce0-f3ae72229e85","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"3d90b977-451c-42ce-a26a-1e32c1082471","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6433ad22-3173-436a-8ff9-355a17142bc5","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e82311a5-f99e-4075-8710-9a76a2da67a7","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"7bebaae0-d5e2-4db3-ab34-644272d2aed6","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2c77a1e3-7af7-4dca-88aa-ee59b41d7896","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"5a3f52b5-5b23-473e-bdbc-906381b9a948","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"299d63a8-7f0b-46a4-a152-9a7ca85a2170","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"906b4555-b1b6-4ddb-a290-c13cb8a153a0","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"efa3eb50-4277-46ee-b451-c5096e889fff","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1d9b7a41-588f-47d1-bb81-f46146e062fb","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"c1687a0f-e3a0-4074-996c-94cbacf49f29","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"269fc082-c3b5-4208-a987-c1a3c1dee620","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"cb76b7de-9b29-4d6b-b644-de3c3c0a1262","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"4dc9d5da-9bf3-4baf-9fae-6b34511062a4","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e4e74cf8-8702-45a6-a536-86d03b66af49","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"abe88d62-9eb6-4113-89f3-abb0cff27c3a","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f3751417-d894-4cf1-8a8c-c2e7391dd4b1","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"49ae1d04-aba0-4311-8548-df297a5aedc0","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"861d71ff-44db-4563-a24c-e1a389708c19","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2f6715bc-8b05-4653-8ec5-0dd835dc6035","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"d2f5c978-f4e4-4c18-ab43-e8fbbc6ad92b","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e183d612-0813-4e0f-b217-308d71c14637","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f8f319ea-ec97-4d69-a862-c6a17191f0f7","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"d895fc4b-55bb-46ee-bd74-7a0abb51650c","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"680fba3c-ad50-41a6-bc56-acd4f229de8f","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1234e85c-d175-4f94-ac83-b53b5e3596d2","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"8fb554aa-3192-402b-9d6b-1ec2b4b99ea0","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"aff9d5e3-7e18-4287-892f-8d7844e9ddf2","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6a23926e-a2ac-4c07-ba79-2f7751f098ee","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"51c9c955-8c66-45d8-bb0c-718aee1073da","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6174a29f-1d15-4a80-a860-d28fdecca505","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"51709a04-b2cd-4465-b2a2-da7beb2e4f15","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"897b8725-abb5-428a-bd7a-a610fae8e28c","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"c00c0609-ac23-4859-8442-be43ed745ad3","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"0996d76b-b253-49a7-9ea8-89d81ce0d42f","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"3c007776-7473-4203-8cc1-9ed79bb512df","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"4bb1ced3-7a10-4719-854b-513729b31e53","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"53155f14-d53e-4d53-a087-0a32d9b088e8","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"279e57c0-0954-49d6-9076-2133a204e24f","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e9c38f09-cfd3-4a78-b2cf-412e3c1b5945","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"754efafe-744e-452e-9e46-833c9b36c763","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"be5fe8a1-1660-4dc4-b7fb-ceb038047086","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]}]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

AGGREGATE

The AGGREGATE function returns an aggregate in a list or database. Users can apply different aggregate functions like COUNT, AVERAGE, SUM, MAX, MIN, etc. to a list while ignoring "error values" and "hidden rows" in the spreadsheets.


The AGGREGATE function is useful, especially when:

  • Users want to apply filters to the data and it is necessary to ignore the cells that were hidden with the filter while adding all the values together.

  • Users want to stop propagating errors throughout the worksheet.

  • Users want to manipulate data such as finding the average of the results of multiple formulas while ignoring errors like "returning invalid numbers" or other types of errors etc.

Syntax

While working with the AGGREGATE function, users can use the following syntax:

  • AGGREGATE(function\_num, options, ref1, [ref2], …)

    This syntax is applicable only when the Function_num argument contains values ranging from 1 to 13. For details, refer to the table shown below.

  • AGGREGATE(function\_num, options, array, k)

    This syntax is applicable only when the Function_num argument contains values ranging from 14 to 19. For details, refer to the table shown below.

Arguments

This function has the following arguments:

Argument

Description

Function_num

[Required], Refers to the number (within the range 1-19) to specify the function that is being used.

Options

[Required], Refers to a particular number that specifies the options to ignore values in the function.

Ref1

[Required], Specifies the reference to a range of cells.

Ref2

[Optional], Specifies the reference to a range of cells.

array

[Required but only when function_num argument above contains values ranging from 14 to 19], Specifies an array, an array formula, or a reference to a range of cells.

k

[Required], This argument is required to specify the functions when the function_num values are ranging from 14-19.

The following things must be kept in mind while using the AGGREGATE function:

  • If the arguments array, k, or ref1 is not provided, the AGGREGATE function will return the #VALUE! error.

  • If argument k is provided as an array, then the first value is used. If argument k is provided as a cell reference with one column, the value in last row is used. ( For e.g. {1,2,3,4}=>1,   A1:A23  => A23, A1:B23 => #VALUE!, A1:C1 => #VALUE)

  • References don't support 3-D references. The function will returns #VALUE! if 3-D references are used (For e.g: "Sheet1:Sheet3!B3" will return #VALUE error.).

  • Ignore hidden doesn't support hidden columns.

  • The rows hidden by filter/ hide/group operations will support the ignore feature.

  • If the array includes a calculation, AGGREGATE function will not ignore hidden rows. (eg: "=AGGREGATE(14,3,A1:A6*123,1)")

Data Types

Accepts a list or a database of numeric values. Returns aggregate calculations like AVERAGE, COUNT, MIN, MAX, etc. while ignoring hidden rows and errors.

Examples

The following image depicts four different examples of the AGGREGATE function:


Let's say you have a spreadsheet with some values in column A and the aggregate functions are applied to cells B1, B2, B3, and B4 as shown in column B below.




The formula output results are displayed in column C and the description of the formula used is mentioned in column D.


The AGGREGATE formula in the cells B1 and B2 used multiple cell references but the formula in the cells B3 and B4 used the array form of the function.