GROUPBY
Description
GROUPBY is a powerful function that can often replace COUNTIF(S), AVERAGEIF(S), and SUMIF(S). GROUPBY aggregates data, and orders the results based on the order that you specify. The grouping is based on a key that you can predefine in the table, or you can define it using columns in the workbook. The result looks similar to a sorted pivot table. This function is often useful as part of headcount planning.
When you group an array of cells that represent some instance values as strings and others as instances (variables from a Workday report), GROUPBY bases the grouping on the instance name, not the instance ID. GROUPBY groups identical names together, even when the instance IDs are different.
Syntax
GROUPBY(
join_by_matrix
, join_by_matrix_column_indexes
, group_by_matrix
, group_by_matrix_column_indexes
, group_by_matrix_aggregate_column_indexes
, [group_by_matrix_aggregate_types]
) - join_by_matrix: The 1-dimensional array of unique identifiers (keys) that identifies the rows to include when grouping.
- join_by_matrix_column_indexes: The starting position of the keys in thejoin_by_matrix, or the set of columns that comprise the matching key (the column values to concatenate to create the primary key).
- group_by_matrix: The array of cells to group.
- group_by_matrix_column_indexes: The position of the column that contains the keys in thegroup_by_matrix.
- group_by_matrix_aggregate_column_indexes: The positions of the columns that contain values to group.
- group_by_matrix_aggregate_types: The operation to perform.Argument ValueOperation0SUM (default)1COUNT of non-null values2COUNT of non-zero numbers3MIN number4MAX number5AVG number
Example
A workbook has 2 sheets named Working and Organization. This is the Working sheet:
A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
1 | Supervisory Org
| Cost Center
| Company
| Location
| Name
| Jan (HC) 2017
| Feb (HC) 2017
| Mar (HC) 2017
| Organization KEY
|
2 | Finance | CC-1 Finance | GMS | Pleasanton | Tim | 1 | 1 | 1 | FinanceCC-1 FinanceGMSPleasanton |
3 | Product Management | CC-2 Product | GMS | Boulder | Josh | 0 | 0 | 1 | Product ManagementCC-2 ProductGMSBoulder |
4 | Development | CC-3 Development | GMS-CA | Toronto | Brian | 1 | 1 | 1 | DevelopmentCC-3 DevelopmentGMS-CAToronto |
5 | Product Management | CC-2 Product | GMS | Boulder | Scott | 0 | 1 | 1 | Product ManagementCC-2 ProductGMSBoulder |
6 | Development | CC-3 Development | GMS-CA | Toronto | Lenny | 1 | 1 | 1 | DevelopmentCC-3 DevelopmentGMS-CAToronto |
7 | Sales | CC-4 Sales | GMS-UK | London | Andy | 0 | 1 | 1 | SalesCC-4 SalesGMS-UKLondon |
8 | Finance | CC-1 Finance | GMS | Pleasanton | Ratna | 1 | 1 | 1 | FinanceCC-1 FinanceGMSPleasanton |
9 | Sales | CC-4 Sales | GMS-UK | Toronto | Aidan | 0 | 0 | 1 | SalesCC-4 SalesGMS-UKToronto |
10 | Sales | CC-4 Sales | GMS-UK | London | Katie | 1 | 1 | 1 | SalesCC-4 SalesGMS-UKLondon |
11 | Total | 5 | 7 | 9 |
The GROUPBY array formula is in cells E2:G6 of the sheet Organization:
=GROUPBY('Organization'!H2:H6,1,'Working'!A2:I10,9,{6, 7, 8})
Alternatively, instead of generating a key separately as shown in column I above and H below, you can specify the columns that you want GROUPBY to use as the key:
=GROUPBY('Organization'!A2:G6,{1,2,3,4},'Working'!A2:H10,{1,2,3,4},{6, 7, 8})
The formula returns these results in sheet Organization:
A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
1 | Supervisory Org
| Cost Center
| Company
| Location
| Jan (HC) 2017
| Feb (HC) 2017
| Mar (HC) 2017
| Organization KEY
|
2 | Finance | CC-1 Finance | GMS | Pleasanton | 2 | 2 | 2 | FinanceCC-1 FinanceGMSPleasanton |
3 | Product Management | CC-2 Product | GMS | Boulder | 0 | 1 | 2 | Product ManagementCC-2 ProductGMSBoulder |
4 | Development | CC-3 Development | GMS-CA | Toronto | 2 | 2 | 2 | DevelopmentCC-3 DevelopmentGMS-CAToronto |
5 | Sales | CC-4 Sales | GMS-UK | London | 1 | 2 | 2 | SalesCC-4 SalesGMS-UKLondon |
6 | Sales | CC-4 Sales | GMS-UK | Toronto | 0 | 0 | 1 | SalesCC-4 SalesGMS-UKToronto |
7 | 5 | 7 | 9 |
Note that row 7 in the sheet Organization is a simple calculation of the total headcount number; it's outside the plan entry area.
Notes
- When doing an operation on date values, GROUPBY converts the date to a serial number before calculating and does not restore the display format to a date. To show a date format, select .
Related Functions
ARRAYAREA