GROUPBY

row_fields

A column-oriented array or range containing the values used to group rows and generate row headers.

• Group rows based on one column: =GROUPBY(A1:A62, E1:E62, SUM)

• Group rows based on multiple columns: =GROUPBY(A1:B62, E1:E62, SUM)

• Group rows based on non-adjacent columns: =GROUPBY(CHOOSECOLS(A1:D62, 1, 4), E1:E62, SUM)

values

A column-oriented array or range of the data to aggregate.

• Aggregate one column: =GROUPBY(A1:A62, E1:E62, SUM)

• Aggregate multiple columns: =GROUPBY(B1:B62, E1:G62, MAX)

• Aggregate non-adjacent columns: =GROUPBY(B1:B62, CHOOSECOLS(E1:G62, 1, 3), MEDIAN)

function

The function to summarize the grouped data.

• Use one function reference: =GROUPBY(A1:A62, E1:E62, SUM)

• Use one LAMBDA function: =GROUPBY(B1:B62, E1:E62, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")))

• Multiple aggregations with HSTACK: =GROUPBY(B1:B62, E1:E62, HSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")), PERCENTOF))

• Multiple aggregations with VSTACK: =GROUPBY(B1:B62, E1:E62, VSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")), PERCENTOF))

• Multiple aggregations with HSTACK and rename aggregation: =GROUPBY(A1:B62, E1:E62, VSTACK(HSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓"))), {"c", "good"}))

• Multiple aggregations with VSTACK and rename aggregation: =GROUPBY(A1:B62, E1:E62, HSTACK(VSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓"))), {"c"; "good"}))

• Multiple aggregations with multiple value columns: The columns and aggregation should have the same count.

• 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)

field_headers [optional]

A number that specifies whether the row_fields and values have headers and whether field headers should be returned in the results.

Options:

Missing(default)- assumes data contains headers based on the values argument

0- no

1- yes and don’t show

2- no but generate

3- yes and show

total_depth[optional]

Determines whether the row headers should contain totals.

Options:

Missing (default)- Grand totals and, where posible, subtotals

0: No totals

1: Grand Totals

2: Grand and Subtotals

sort_order

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.

Missing (default) : Ascending order from A to Z based on the row_fields values.

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

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

Specifies the relationship fields when multiple columns are provided to row_fields.

Options:

0: Hierarchy (default) - Sorting of later field columns takes into account the hierarchy of earlier columns.

1: Table - Sorting of each field column is done independently. Subtotals are not supported as they rely on the data having a hierarchy.