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 grouping of your data along two axis and aggregates the associated values. |
row_fields | A column-oriented array or range that contains the values which are used to group rows and generate row headers. |
col_fields | A column-oriented array or range that contains the values which are used to group columns and generate column headers. |
values | A column-oriented array or range of the data to aggregate. |
function | The function that defines how to aggregate the values: SUM PERCENTOF AVERAGE MEDIAN COUNT COUNTA MAX MIN PRODUCT ARRAYTEXT CONCAT STDEV.S STDEV.P VAR.S VAR.P MODE.SNGL LAMBDA |
field_headers | (Optional) A number that specifies whether the row_fields, col_fields and values have headers and whether field headers should be returned in the results: 0 = No 1 = Yes but don't show 2 = No but generate 3 = Yes and show |
row_total_depth | (Optional) Determines whether the row headers should contain totals: 0 = No totals 1 = Grand totals 2 = Grand and subtotals -1 = Grand totals at top -2 = Grand and subtotals at top |
row_sort_order | (Optional) A number indicating how rows should be sorted. |
col_total_depth | (Optional) Determines whether the column headers should contain totals: 0 = No totals 1 = Grand totals 2 = Grand and subtotals -1 = Grand totals at top -2 = Grand and subtotals at top |
col_sort_order | (Optional) A number indicating how rows should be sorted. |
filter_array | (Optional) A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered. |
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 create a Dynamic Array Formula. * You can use the GETPIVOTDATA function to return the data obtained from a pivot table. * You can use the GROUPBY function to return the grouping of your data along one axis and aggregates the associated values. * You can use the PERCENTOF function to return the percentage that a subset makes up from a given data set. * link - insider.microsoft365.com/en-us/blog/new-aggregation-functions-in-excel-groupby-and-pivotby * This function was first released in December 2023. * For the Microsoft documentation refer to support.microsoft.com |
|
© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited Top