PIVOTBY
Groups rows and columns and aggregates the values: excel's argument list; `function` is SUM, COUNT, AVERAGE, MAX, MIN or PRODUCT, written as in excel (SUM), as text or as a selector; a LAMBDA is refused.
- Excel: Differs in detail
- Google Sheets: No equivalent
- Returns: Array
Examples
=PIVOTBY(A1:A4, B1:B4, C1:C4, SUM) | A | B | C | |
|---|---|---|---|
| 1 | north | Q1 | 1 |
| 2 | south | Q1 | 2 |
| 3 | north | Q2 | 3 |
| 4 | south | Q2 | 4 |
Result
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])row_fields- range required
col_fields- range required
values- range required
function- any required
field_headers- number optional
row_total_depth- number optional
row_sort_order- number optional
col_total_depth- number optional
col_sort_order- number optional
filter_array- range optional
relative_to- number optional
Errors
#VALUE!=PIVOTBY(1, 2, A1:A3, 4)
Related
DAVERAGEAverage of database values matching criteria. DCOUNTCounts numeric database values matching criteria. DCOUNTACounts non-empty database values matching criteria. DGETThe single database value matching criteria. DMAXLargest database value matching criteria. DMINSmallest database value matching criteria.