MS.GROUPBY
Description
Summarizes data by grouping, aggregating, sorting, and filtering based on specified fields. This function is the same as WD.GROUPBY but
without
a style parameter. It is useful for creating summary tables and performing aggregations similar to a pivot table but directly within a formula.
Syntax
MS.GROUPBY(
row_fields
, values
, function
, [field_headers
], [total_depth
], [sort_order
], [filter_array
], [field_relationship
]) - row_fields: The range of values to group.
- values: The values to aggregate.
- function: The calculation to run when aggregating values.
- field_headers: A number that specifies whether the data has headers and whether you want to include them in the results. Options include:
- Omitted value (default): Headers are not shown.
- 0: No headers.
- 1: Yes, but don’t show headers.
- 2: No headers, but generate them.
- 3: Yes, and show headers.
- total_depth: A number that specifies whether to show totals and subtotals. For subtotals,row_fieldsmust have at least 2 columns. Options include:
- Omitted value (default): Grand totals and, where possible, subtotals.
- 0: No totals.
- 1: Grand totals at the bottom.
- 2: Grand totals and subtotals at the bottom.
- -1: Grand totals at the top.
- -2: Grand totals and subtotals at the top.
- sort_order: A number that indicates how rows should be sorted. Numbers correspond to columns in row_fields, followed by columns in values. A positive number sorts rows in ascending order. A negative number sorts rows in descending order.
- filter_array: A column-oriented array of booleans that indicates which rows should be included or excluded from the output.
- field_relationship: A number that specifies the relationship between columns when multiple columns are provided asrow_fieldsand affects the sorting of the results. Options include:
- 0: Hierarchy (default) - Sortsrow_fieldsfrom left to right where the sorting of later columns takes into account the hierarchy of earlier columns.
- 1: Table - Sorts each column independently.