PIVOTBY

PIVOTBY(row_fields ,col_fields ,values ,function [,field_headers] [,row_total_depth] [,row_sort_order]

[,col_total_depth] [,col_sort_order] [,filter_array][,relative_to])

Returns the summary table after grouping and aggregating by row and column.

row_fields	The range of cells you want to group by rows.
col_fields	The range of cells you want to group by columns.
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)
row_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
row_sort_order	(Optional) The column number indicating how rows should be sorted: Ascending = positive numbers (default) Descending = negative numbers
col_total_depth	(Optional) Determines whether the column 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
col_sort_order	(Optional) The column number indicating how columns 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.
relative_to	(Optional) Controls which values are provided to the 2nd argument of the aggregation function: 0 = Column totals (default) 1 = Row totals 2 = Grand totals 3 = Parent column total 4 = Parent row total

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 group the data in rows and columns. For only rows you need to use the GROUPBY 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 GROUPBY function to return the summary table after grouping and aggregating by row.
* You can use the PERCENTOF function to return the percentage of a value compared to the total.
* For the Microsoft documentation refer to support.microsoft.com

	A	B	C	D
1	=PIVOTBY(B1:B4, C1:C4, D1:D4, SUM, 0,0,,0) = { , c1, c2 ; r1, 10, 30 ; r2 , 20, 40 }	r1	c1	10
2	=PIVOTBY(B1:B4, C1:C4, D1:D4, COUNT, 0,0,,0) = { , c1, c2 ; r1, 1, 1 ; r2 , 1, 1 }	r2	c1	20
3	=PIVOTBY(B1:B4, C1:C4, D1:D4, PERCENTOF, 0,0,,0) = { , c1, c2 ; r1, 0.3, 0.4 ; r2 , 0.6, 0.5 }	r1	c2	30
4	=PIVOTBY(B1:B4, C1:C2, D1:D2, SUM) = #VALUE!	r2	c2	40

1 - Display the total value of all the items, using column "B" for row grouping and column "C" for column grouping. The results table has no grand totals.
2 - Display the count of all the items, using column "B" for row grouping and column "C" for column grouping.
3 - Display the percentage of all the items, using column "B" for row grouping and column "C" for column grouping.
4 - When the "row_fields", "col_fields" and "values" do not have the same number of rows.