Description
Group the data along an axis and apply a Lambda function to the resulting group.
NOTE
- The [row_fields] and [values] arguments can be multi-column array.
- Common error:
– The function will return a #VALUE! when you providing an invalid Lambda function.
Syntax
Copied!=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array]), [filter_relationship])
SAMPLE data
| | A | B | C | D |
|-----|------|-----------|------------|-------|
| 1 | Year | Category | Item | Sales |
| 2 | 2017 | Component | Chains | 20000 |
| 3 | 2015 | Clothing | Socks | 3700 |
| 4 | 2017 | Clothing | Bib-Shorts | 4000 |
| 5 | 2015 | Clothing | Shorts | 13300 |
| 6 | 2017 | Clothing | Tights | 36000 |
| 7 | 2015 | Component | Handlebars | 2300 |
| 8 | 2016 | Clothing | Socks | 2300 |EXAMPLE 1
Copied!//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=𝘢𝘶𝘵𝘰[0,3], 𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=1[-2,2], 𝘴𝘰𝘳𝘵_𝘰𝘳𝘥𝘦𝘳=[>=0,<0], 𝘧𝘪𝘭𝘵𝘦𝘳_𝘢𝘳𝘳𝘢𝘺=[𝘤𝘰𝘭𝘶𝘮𝘯 𝘣𝘰𝘰𝘭𝘦𝘢𝘯], 𝘧𝘪𝘭𝘵𝘦𝘳_𝘳𝘦𝘭𝘢𝘵𝘪𝘰𝘯𝘴𝘩𝘪𝘱=0[0,1]. //𝘨𝘦𝘯𝘦𝘳𝘢𝘵𝘦 𝘢 𝘴𝘶𝘮𝘮𝘢𝘳𝘺 𝘰𝘧 𝘵𝘰𝘵𝘢𝘭 𝘴𝘢𝘭𝘦𝘴 𝘣𝘺 𝘤𝘢𝘵𝘦𝘨𝘰𝘳𝘺 𝘧𝘰𝘳 𝘦𝘢𝘤𝘩 𝘺𝘦𝘢𝘳. //𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=𝘢𝘶𝘵𝘰, 𝘵𝘩𝘦 𝘷𝘢𝘭𝘶𝘦𝘴 𝘩𝘢𝘷𝘦 𝘩𝘦𝘢𝘥𝘦𝘳𝘴 𝘣𝘶𝘵 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵. =GROUPBY(A1:B8, D1:D8, SUM)
| | A | B | C |
|---|---------|-------------|--------|
| 1 | 2015 | Clothing | 17000 |
| 2 | 2015 | Component | 2300 |
| 3 | 2016 | Clothing | 2300 |
| 4 | 2017 | Clothing | 40000 |
| 5 | 2017 | Component | 20000 |
| 6 | ------- | ----------- | -------|
| 7 | Total | | 81600 |EXAMPLE 2
Copied!//𝘤𝘢𝘭𝘤𝘶𝘭𝘢𝘵𝘦𝘥 𝘸𝘪𝘵𝘩 𝘤𝘶𝘴𝘵𝘰𝘮 𝘭𝘢𝘮𝘣𝘥𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯. //𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=1, 𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=0. 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘩𝘦𝘢𝘥𝘦𝘳𝘴 𝘢𝘯𝘥 𝘨𝘳𝘢𝘯𝘥 𝘵𝘰𝘵𝘢𝘭s 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵. =GROUPBY(A1:B8, D1:D8, LAMBDA(a, IF(SUM(a)>=20000, "great sales", "bad sales")), 1, 0)
| | A | B | C |
|---|------|-----------|-------------|
| 1 | 2015 | Clothing | bad sales |
| 2 | 2015 | Component | bad sales |
| 3 | 2016 | Clothing | bad sales |
| 4 | 2017 | Clothing | great sales |
| 5 | 2017 | Component | great sales |EXAMPLE 3
Copied!//𝘢𝘱𝘱𝘭𝘺 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘭𝘢𝘮𝘣𝘥𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯𝘴 𝘵𝘰 𝘦𝘢𝘤𝘩 𝘨𝘳𝘰𝘶𝘱. //𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=3, 𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=2. 𝘭𝘦𝘵 𝘴𝘩𝘰𝘸 𝘩𝘦𝘢𝘥𝘦𝘳𝘴, 𝘨𝘳𝘢𝘯𝘥 𝘢𝘯𝘥 𝘴𝘶𝘣𝘵𝘰𝘵𝘢𝘭𝘴 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵. =GROUPBY(A1:B8, D1:D8, HSTACK(SUM, AVERAGE, MAX), 3, 2, , , )
| | A | B | C | D | E |
|----|-------------|-----------|-------|-------------|-------|
| 1 | | | SUM | AVERAGE | MAX |
| 2 | Year | Category | Sales | Sales | Sales |
| 3 | 2015 | Clothing | 17000 | 8500 | 13300 |
| 4 | 2015 | Component | 2300 | 2300 | 2300 |
| 5 | 2015 | | 19300 | 6433.333333 | 13300 |
| 6 | 2016 | Clothing | 2300 | 2300 | 2300 |
| 7 | 2016 | | 2300 | 2300 | 2300 |
| 8 | 2017 | Clothing | 40000 | 20000 | 36000 |
| 9 | 2017 | Component | 20000 | 20000 | 20000 |
| 10 | 2017 | | 60000 | 20000 | 36000 |
| 11 | Grand Total | | 81600 | 11657.14286 | 36000 |