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
| Argument | Required | Description |
|---|---|---|
row_fields | Required | The column(s) whose values become rows |
col_fields | Required | The column(s) whose values become columns |
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 |
row_total_depth | Optional | 0 no row totals, 1 grand totals (default); negative puts them on top |
row_sort_order | Optional | Row field number to sort by, negative for descending |
col_total_depth | Optional | 0 no column totals, 1 grand totals (default); negative puts them first |
col_sort_order | Optional | Column field number to sort by, negative for descending |
filter_array | Optional | TRUE/FALSE per row to include |
relative_to | Optional | For PERCENTOF: 0 column totals (default), 1 row totals, 2 grand total |
How to use PIVOTBY
When PIVOTBY returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
PIVOTBY takes 4 required arguments and 7 optional arguments. Type =PIVOTBY( 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.
=PIVOTBY({"a";"b";"a"}, {"x";"x";"y"}, {1;2;3}, SUM)Result Spilled result
| A | B | C | D | |
|---|---|---|---|---|
| 1 | x | y | Total | |
| 2 | a | 1 | 3 | 4 |
| 3 | b | 2 | 2 | |
| 4 | Total | 3 | 3 | 6 |
Related array functions
- 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.
- QUERYRuns a query (select, where, group by, order by, limit, offset, label) over a range with optional header rows.
- REDUCEReduces an array to one value by applying a LAMBDA to an accumulator and each value.
- SCANApplies a LAMBDA to each value with a running accumulator and returns every intermediate result.
- SEQUENCEReturns a list of sequential numbers in an array of the given size.
- SORTSorts the rows or columns of a range or array.