# GROUPBY
## Content
This function allows you to create a summary of your data using formula. It supports grouping along row fields and aggregating the associated values. Additionally, it can perform sorting and filtering on grouped data.
> type=note
> GROUPBY is a dynamic array formula, and you need to enable the dynamic array feature in the Workbook.
## Syntax
`GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])`
## Arguments
The function has following arguments.
| **Argument** | **Description** | **Example** |
| -------- | ----------- | ------- |
| *row\_fields* | A column-oriented array or range containing the values used to group rows and generate row headers. | - Group rows based on one column: `=GROUPBY(A1:A62, E1:E62, SUM)`
- Group rows based on multiple columns: `=GROUPBY(A1:B62, E1:E62, SUM)`
- Group rows based on non-adjacent columns: `=GROUPBY(CHOOSECOLS(A1:D62, 1, 4), E1:E62, SUM)`
|
| *values* | A column-oriented array or range of the data to aggregate. | - Aggregate one column: `=GROUPBY(A1:A62, E1:E62, SUM)`
- Aggregate multiple columns: `=GROUPBY(B1:B62, E1:G62, MAX)`
- Aggregate non-adjacent columns: `=GROUPBY(B1:B62, CHOOSECOLS(E1:G62, 1, 3), MEDIAN)`
|
| *function* | The function to summarize the grouped data. | - Use one function reference: `=GROUPBY(A1:A62, E1:E62, SUM)`
- Use one LAMBDA function: `=GROUPBY(B1:B62, E1:E62, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")))`
- Multiple aggregations with HSTACK: `=GROUPBY(B1:B62, E1:E62, HSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")), PERCENTOF))`
- Multiple aggregations with VSTACK: `=GROUPBY(B1:B62, E1:E62, VSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓")), PERCENTOF))`
- Multiple aggregations with HSTACK and rename aggregation: `=GROUPBY(A1:B62, E1:E62, VSTACK(HSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓"))), {"c", "good"}))`
- Multiple aggregations with VSTACK and rename aggregation: `=GROUPBY(A1:B62, E1:E62, HSTACK(VSTACK(COUNT, LAMBDA(a, IF(COUNT(a) > 2, "↑", "↓"))), {"c"; "good"}))`
- Multiple aggregations with multiple value columns: The columns and aggregation should have the same count.
- Multiple aggregations with temp LAMBDA functions: `=GROUPBY(A1:A13, C1:C13, VSTACK(MAP({1, 2, 3, 4}, LAMBDA(_m, LAMBDA(_x, @INDEX(_x, _m) / SUM(_x)))), "2024 Q" & {1, 2, 3, 4}), 1, 0)`
|
| *field\_headers [optional]* | A number that specifies whether the row\_fields and values have headers and whether field headers should be returned in the results.
Options:
Missing(default)- assumes data contains headers based on the values argument
0- no
1- yes and don’t show
2- no but generate
3- yes and show | |
| *total\_depth[optional]* | Determines whether the row headers should contain totals.
Options:
Missing (default)- Grand totals and, where posible, subtotals
0: No totals
1: Grand Totals
2: Grand and Subtotals | |
| *sort\_order* | A number indicating how rows should be sorted. Numbers correspond with columns in row\_fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order.
Missing (default) : Ascending order from A to Z based on the row\_fields values.
Sorting by multiple columns: Supply a one-dimensional array of numbers corresponding to columns in row\_fields followed by the columns in values. | |
| *filter\_array* | A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered. | =GROUPBY(B:B, E:E, COUNT, , , , G:G > 200) |
| *field\_relationship* | Specifies the relationship fields when multiple columns are provided to row\_fields.
Options:
0: Hierarchy (default) - Sorting of later field columns takes into account the hierarchy of earlier columns.
1: Table - Sorting of each field column is done independently. Subtotals are not supported as they rely on the data having a hierarchy. | |
## Remarks
The GROUPBY function supports Excel import and export.
## Example
`GROUPBY(A1:A62, E1:E62, SUM)`
The following code shows the usage of GROUPBY function on a set of data.
```javascript
// Allow the Dynamic Array to True
spread.options.allowDynamicArray = true;
spread.setSheetCount(3);
let sheet2 = spread.getSheet(1);
sheet2.name("GROUPBY 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],
];
// Sheet2 - GROUPBY Function
sheet2.setArray(0, 0, data);
sheet2.tables.add("table2", 0, 0, data.length, 6, GC.Spread.Sheets.Tables.TableThemes.medium2);
sheet2.setFormula(2, 8, "=GROUPBY(A2:A26,E2:E26, SUM)");
```