PIVOTBY

This function is a powerful tool that allows you to create a summary of your data using a formula. It supports grouping along two axes and aggregating the associated values. This function is like the GROUPBY function but with the added capability of grouping data by both rows and columns.

type=note
PIVOTBY is a dynamic array formula, and you need to enable the dynamic array feature in the Workbook.

Syntax

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

Arguments

The function has following arguments.

Arguments	Description
row_fields (required)	A column-oriented array or range that contains the values used to group rows and generate row headers.
col_fields (required)	A column-oriented array or range that contains the values used to group columns and generate column headers.
values (required)	A column-oriented array or range of the data to aggregate.
function (required)	A lambda function or eta reduced lambda (e.g., SUM, AVERAGE, COUNT) that defines how to aggregate the values.
field_headers	Specifies whether the row_fields, col_fields, and values have headers and whether field headers should be returned in the results.
row_total_depth	Determines whether the row headers should contain totals.
row_sort_order	Indicates how rows should be sorted.
col_total_depth	Determines whether the column headers should contain totals.
col_sort_order	Indicates how columns should be sorted.
filter_array	A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered.
relative_to	Controls which values are provided to the second argument of the aggregation function, typically used with the PERCENTOF function.

Remarks

PIVOTBY function supports Excel import and export.
PIVOTBY is a dynamic array function which automatically spills the results into as many cells as needed.

Example

PIVOTBY (B2:B34,A2:A34,D2:D34,SUM)

The following code shows the usage of PIVOTBY on a set of data.

 // Allow the Dynamic Array to True
 spread.options.allowDynamicArray = true;
 spread.setSheetCount(3);
 let sheet1 = spread.getSheet(0);
 sheet1.name("PIVOTBY Function");
 let data = [
 ["YEAR", "Category", "Product", "Status", "Sales", "Rating"],
 [2023, "Electronics", "Smart TV", "Active", 15000, 4.5],
 [2023, "Fashion", "Designer Jeans", "Discontinued", 8000, 4.7],
 [2024, "Food", "Organic Granola", "Active", 5000, 4.8],
 [2023, "Books", "Bestseller Novel", "Active", 12000, 4.6],
 [2024, "Electronics", "Laptop", "Active", 20000, 4.9],
 [2023, "Beauty", "Skincare Set", "Discontinued", 7000, 4.4],
 [2023, "Home & Garden", "Garden Tools", "Active", 6500, 4.3],
 [2024, "Health", "Fitness Tracker", "Active", 9500, 4.6],
 [2023, "Toys", "Action Figure", "Active", 4800, 4.7],
 [2024, "Automotive", "Car Accessories", "Discontinued", 3200, 4.5],
 [2023, "Sports", "Basketball", "Active", 7600, 4.8],
 [2024, "Office Supplies", "Notebooks", "Active", 11000, 4.4],
 [2023, "Pet Supplies", "Dog Food", "Discontinued", 5600, 4.6],
 [2024, "Music", "Headphones", "Active", 13000, 4.9],
 [2023, "Outdoor", "Camping Tent", "Discontinued", 4400, 4.5],
 [2024, "Jewelry", "Silver Necklace", "Active", 2800, 4.7],
 [2023, "Tools", "Power Drill", "Active", 3900, 4.4],
 [2024, "Baby", "Stroller", "Active", 1700, 4.6],
 [2023, "Kitchen", "Blender", "Active", 2500, 4.8],
 [2024, "Clothing", "Casual Shirt", "Discontinued", 6200, 4.5],
 [2023, "Art", "Oil Paintings", "Active", 1900, 4.7],
 [2024, "Hobbies", "Model Trains", "Active", 3100, 4.4],
 [2023, "Tech Gadgets", "Smart Watch", "Discontinued", 7300, 4.6],
 [2024, "Travel", "Luggage", "Active", 4600, 4.8],
 [2023, "Home Decor", "Wall Clock", "Active", 2200, 4.5],
 ];
 // Sheet1 - PIVOTBY Function
 sheet1.setArray(0,0, data);
 sheet1.tables.add("table1",0,0, data.length,6, GC.Spread.Sheets.Tables.TableThemes.medium2);
 sheet1.setFormula(2,8, "=PIVOTBY(B2:B10,A2:A10, E2:E10,SUM)");