Syntax
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])Arguments
| Argument | Required | Description |
|---|---|---|
row_fields | Required | The column(s) to group by |
values | Required | The column(s) to aggregate |
function | Required | A function name such as SUM, or a LAMBDA |
field_headers | Optional | 0 no headers, 1 headers not shown, 2 no headers but generate them, 3 headers shown |
total_depth | Optional | 0 no totals, 1 grand total (default), 2 subtotals too; negative puts them on top |
sort_order | Optional | Column number to sort by, negative for descending |
filter_array | Optional | TRUE/FALSE per row to include |
field_relationship | Optional | 0 hierarchy (default), 1 table |
How to use GROUPBY
When GROUPBY returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
GROUPBY takes 3 required arguments and 5 optional arguments. Type =GROUPBY( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=GROUPBY({"a";"b";"a"}, {1;2;3}, SUM)Result Spilled result
| A | B | |
|---|---|---|
| 1 | a | 4 |
| 2 | b | 2 |
| 3 | Total | 6 |
=GROUPBY({"x";"y";"x"}, {5;7;1}, LAMBDA(v, MAX(v)), 0, 0, -2)Result Spilled result
| A | B | |
|---|---|---|
| 1 | y | 7 |
| 2 | x | 5 |
Related array functions
- DROPRemoves a number of rows or columns from the start (positive) or end (negative) of an array.
- EXPANDExpands an array to the given number of rows and columns, filling new cells with a pad value.
- FILTERReturns the rows (or columns) of a range or array that meet a condition.
- FLATTENPuts the values of one or more ranges into a single column, reading row by row.
- HSTACKPlaces arrays side by side; shorter columns are padded with #N/A.
- MAKEARRAYBuilds an array of the given size by calling a LAMBDA with each row and column number.
- MAPApplies a LAMBDA to each value of one or more arrays and returns the results.
- PERCENTOFReturns the sum of a subset of values as a fraction of the sum of all values.
- PIVOTBYSummarizes data by row and column fields and aggregates the values at each intersection.