# GROUPBY

## Content

This function allows you to create a summary of your data using formula. It supports grouping along row fields and aggregating the associated values. Additionally, it can perform sorting and filtering on grouped data.

>type=note
> **Note**: GROUPBY is a dynamic array formula, and you need to enable the dynamic array feature in the Workbook.

## Syntax

`GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])`

## Arguments

The function has following arguments.

| **Argument** | **Description** | **Example** |
| -------- | ----------- | ------- |
| *row\_fields* | [Required] A column-oriented array or range containing the values used to group rows and generate row headers. | <ul><li>Group rows based on one column: `=GROUPBY(A1:A62, E1:E62, SUM)`</li><li>Group rows based on multiple columns: `=GROUPBY(A1:B62, E1:E62, SUM)`</li><li>Group rows based on non-adjacent columns: `=GROUPBY(CHOOSECOLS(A1:D62, 1, 4), E1:E62, SUM)`</li></ul> |
| *values* | [Required] A column-oriented array or range of the data to aggregate. | <ul><li>Aggregate one column: `=GROUPBY(A1:A62, E1:E62, SUM)`</li><li>Aggregate multiple columns: `=GROUPBY(B1:B62, E1:G62, MAX)`</li><li>Aggregate non-adjacent columns: `=GROUPBY(B1:B62, CHOOSECOLS(E1:G62, 1, 3), MEDIAN)`</li></ul> |
| *function* | [Required] The function to summarize the grouped data. | <ul><li>Use one function reference: `=GROUPBY(A1:A62, E1:E62, SUM)`</li><li>Use one LAMBDA function: `=GROUPBY(B1:B62, E1:E62, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")))`</li><li>Multiple aggregations with HSTACK: `=GROUPBY(B1:B62, E1:E62, HSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")), PERCENTOF))`</li><li>Multiple aggregations with VSTACK: `=GROUPBY(B1:B62, E1:E62, VSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")), PERCENTOF))`</li><li>Multiple aggregations with HSTACK and rename aggregation: `=GROUPBY(A1:B62, E1:E62, VSTACK(HSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓"))), {"c", "good"}))`</li><li>Multiple aggregations with VSTACK and rename aggregation: `=GROUPBY(A1:B62, E1:E62, HSTACK(VSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓"))), {"c"; "good"}))`</li><li>Multiple aggregations with multiple value columns: The columns and aggregation should have the same count.</li><li>Multiple aggregations with temp LAMBDA functions: `=GROUPBY(A1:A13, C1:C13, VSTACK(MAP({1, 2, 3, 4}, LAMBDA(_m, LAMBDA(_x, @INDEX(_x, _m) / SUM(_x)))), "2024 Q" & {1, 2, 3, 4}), 1, 0)`</li></ul> |
| *field\_headers [optional]* | [Optional] A number that specifies whether the row\_fields and values have headers and whether field headers should be returned in the results.<br>Options:<br>Missing(default)- assumes data contains headers based on the values argument<br>0- no<br>1- yes and don’t show<br>2- no but generate<br>3- yes and show |  |
| *total\_depth[optional]* | [Optional] Determines whether the row headers should contain totals.<br>Options:<br>Missing (default)- Grand totals and, where posible, subtotals<br>0: No totals<br>1: Grand Totals<br>2: Grand and Subtotals |  |
| *sort\_order* | [Optional] A number indicating how rows should be sorted. Numbers correspond with columns in row\_fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order.<br>Missing (default) : Ascending order from A to Z based on the row\_fields values.<br>Sorting by multiple columns: Supply a one-dimensional array of numbers corresponding to columns in row\_fields followed by the columns in values. |  |
| *filter\_array* | [Optional] A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered. | =GROUPBY(B:B, E:E, COUNT, , , , G:G > 200) |
| *field\_relationship* | [Optional] Specifies the relationship fields when multiple columns are provided to row\_fields.<br>Options:<br>0: Hierarchy (default) - Sorting of later field columns takes into account the hierarchy of earlier columns.<br>1: Table - Sorting of each field column is done independently. Subtotals are not supported as they rely on the data having a hierarchy. |  |

## Remarks

The GROUPBY function supports Excel import and export.

## Example

`GROUPBY(A1:A62, E1:E62, SUM)`