GROUPBY

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

Returns the summary table after grouping and aggregating by row.

row_fields	The range of cells you want to group.
values	The range of cells you want to aggregate.
function	The LAMBDA function or Eta Reduced Lambda to use for the aggregation.
field_headers	(Optional) Whether the "row_fields" and "values" have headers: 0 = no headers 1 = yes, but don't show 2 = no headers, but generate them 3 = yes and show (default when first row is text)
total_depth	(Optional) Determines whether the row headers should contain totals: 0 = no totals 1 = Grand totals at bottom 2 = Grand and subtotals at bottom (default) -1 = Grand totals at top -2 = Grand totals and subtotals at top
sort_order	(Optional) The column number indicating how rows should be sorted: Ascending = positive numbers (default) Descending = negative numbers
filter_array	(Optional) A logical expression that returns a one dimensional array of boolean values of the same length.
field_relationship	(Optional) The relationship fields when multiple columns are provided to "row_fields": 0 = Hierarchy (default) 1 = Table

REMARKS

* For an illustrated example refer to the page under Data Functions
* This function was added in Microsoft 365 (version 2409).
* This function can perform filtering and sorting similar to a pivottable but without the automatic formatting.
* This function can only group the data in rows. For rows and columns you need to use the PIVOTBY function.
* This function can accept the following Eta Reduced Lambda: AVERAGE, ARRAYTOTEXT, CONCAT, COUNT, COUNTA, MAX, MEDIAN, MIN, PERCENTOF, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P, MODE.SNGL.
* If "row_fields" contains a different number of rows to "values", then #VALUE! is returned.
* If "field_headers" is left blank, when the first value is text and the second value is a number and there are multiple row or column group levels, then field headers will be displayed.
* If "total_depth" is left blank, grand total are usually displayed. Subtotals are usually displayed when "row_fields" has at least 2 columns.
* The "sort_order" is the columns in "row_fields" followed by the columns in "values".
* If "sort_order" is left blank, then the group column is sorted in ascending order.
* If "filter_array" is left blank, then no filtering is done.
* If "field_relationship" = 0, then sorting of later field columns takes into account the hierarchy of earlier columns.
* If "field_relationship" = 1, then sorting of each field column is done independently. Subtotals are not supported as they rely on the data having a hierarchy.
* Always have your data in an Excel table so any new data is automatically displayed.
* You can use the ARRAYTOTEXT function to return the contents of an array or cell range converted to text.
* You can use the GETPIVOTDATA function to return the data obtained from a pivot table.
* You can use the PERCENTOF function to return the percentage of a value compared to the total.
* You can use the PIVOTBY function to return the summary table after grouping and aggregating by row and column.
* For the Microsoft documentation refer to support.microsoft.com

	A	B	C
1	=GROUPBY(B1:B4, C1:C4, SUM) = { one, 30 ; two, 80 ; Total, 110 }	two	30
2	=GROUPBY(B1:B4, C1:C4, COUNT) = { one, 2 ; two, 2 ; Total, 4 }	one	20
3	=GROUPBY(B1:B4, C1:C4, AVERAGE) = { one, 15 ; two, 40 ; Total, 27.5 }	two	50
4	=GROUPBY(B1:B4, C1:C2, SUM) = #VALUE!	one	10

1 - Display the total value of all the items grouping by column "B". The table is automatically sorted.
2 - Display the count of all the items grouping by column "B".
3 - Display the average of all the items grouping by column "B".
4 - When the "row_fields" and "values" do not have the same number of rows.