# PIVOTBY

## Content

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.

```javascript
 // 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)");
```