PIVOTBY
groups rows and columns and aggregates the values — excel's argument list; `function` takes an aggregation NAME or selector, not a function reference · from excel · excel: differs in detail · no sheets equivalent
signature
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])
returns: array
| argument | required |
|---|---|
| row_fields | required |
| col_fields | required |
| values | required |
| function | required |
| field_headers | optional |
| row_total_depth | optional |
| row_sort_order | optional |
| col_total_depth | optional |
| col_sort_order | optional |
| filter_array | optional |
| relative_to | optional |
examples
formula and result, taken from the engine's own test suite — every result below is asserted in CI, not written by hand.
| A | B | C | |
|---|---|---|---|
| 1 | 10 | 2 | alpha |
| 2 | 20 | 4 | beta |
| 3 | 30 | 6 | gamma |
fx
=PIVOTBY(A1:A3, B1:B3, C1:C3, 1) errors
error values observed from the engine — each with a call that produces it.
| error | produced by |
|---|---|
| #VALUE! | =PIVOTBY(1, 2, A1:A3, 4) |
related
- DAVERAGE average of database values matching criteria
- DCOUNT counts numeric database values matching criteria
- DCOUNTA counts non-empty database values matching criteria
- DGET the single database value matching criteria
- DMAX largest database value matching criteria
- DMIN smallest database value matching criteria