PIVOTBY Function

Description

Group the data along two axis and apply a Lambda function to the resulting group.

NOTE

• The [row_fields], [col_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!=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])
=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])

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],
𝘤𝘰𝘭_𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=1[-2,2], 𝘳𝘤𝘰𝘭_𝘴𝘰𝘳𝘵_𝘰𝘳𝘥𝘦𝘳=[>=0,<0], 𝘧𝘪𝘭𝘵𝘦𝘳_𝘢𝘳𝘳𝘢𝘺=[𝘤𝘰𝘭𝘶𝘮𝘯 𝘣𝘰𝘰𝘭𝘦𝘢𝘯], 𝘳𝘦𝘭𝘢𝘵𝘪𝘷𝘦_𝘵𝘰=0[0,4].

//𝘨𝘦𝘯𝘦𝘳𝘢𝘵𝘦 𝘢 𝘴𝘶𝘮𝘮𝘢𝘳𝘺 𝘰𝘧 𝘵𝘰𝘵𝘢𝘭 𝘴𝘢𝘭𝘦𝘴 𝘣𝘺 𝘤𝘢𝘵𝘦𝘨𝘰𝘳𝘺 𝘧𝘰𝘳 𝘦𝘢𝘤𝘩 𝘺𝘦𝘢𝘳.
//𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=𝘢𝘶𝘵𝘰, 𝘵𝘩𝘦 𝘷𝘢𝘭𝘶𝘦𝘴 𝘮𝘢𝘺 𝘩𝘢𝘷𝘦 𝘩𝘦𝘢𝘥𝘦𝘳𝘴 𝘣𝘶𝘵 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵.
=PIVOTBY(B1:B8, A1:A8, D1:D8, SUM)
//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=𝘢𝘶𝘵𝘰[0,3], 𝘳𝘰𝘸_𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=1[-2,2], 𝘳𝘰𝘸_𝘴𝘰𝘳𝘵_𝘰𝘳𝘥𝘦𝘳=[>=0,<0],
𝘤𝘰𝘭_𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=1[-2,2], 𝘳𝘤𝘰𝘭_𝘴𝘰𝘳𝘵_𝘰𝘳𝘥𝘦𝘳=[>=0,<0], 𝘧𝘪𝘭𝘵𝘦𝘳_𝘢𝘳𝘳𝘢𝘺=[𝘤𝘰𝘭𝘶𝘮𝘯 𝘣𝘰𝘰𝘭𝘦𝘢𝘯], 𝘳𝘦𝘭𝘢𝘵𝘪𝘷𝘦_𝘵𝘰=0[0,4].

//𝘨𝘦𝘯𝘦𝘳𝘢𝘵𝘦 𝘢 𝘴𝘶𝘮𝘮𝘢𝘳𝘺 𝘰𝘧 𝘵𝘰𝘵𝘢𝘭 𝘴𝘢𝘭𝘦𝘴 𝘣𝘺 𝘤𝘢𝘵𝘦𝘨𝘰𝘳𝘺 𝘧𝘰𝘳 𝘦𝘢𝘤𝘩 𝘺𝘦𝘢𝘳.
//𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=𝘢𝘶𝘵𝘰, 𝘵𝘩𝘦 𝘷𝘢𝘭𝘶𝘦𝘴 𝘮𝘢𝘺 𝘩𝘢𝘷𝘦 𝘩𝘦𝘢𝘥𝘦𝘳𝘴 𝘣𝘶𝘵 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵.
=PIVOTBY(B1:B8, A1:A8, D1:D8, SUM)

|   | A         |     B |    C |     D |     E |
|---|-----------|-------|------|-------|-------|
| 1 |           |  2015 | 2016 |  2017 | Total |
| 2 | Clothing  | 17000 | 2300 | 40000 | 59300 |
| 3 | Component |  2300 |      | 20000 | 22300 |
| 4 | Total     | 19300 | 2300 | 60000 | 81600 |

EXAMPLE 2

Copied!//𝘤𝘢𝘭𝘤𝘶𝘭𝘢𝘵𝘦𝘥 𝘸𝘪𝘵𝘩 𝘤𝘶𝘴𝘵𝘰𝘮 𝘭𝘢𝘮𝘣𝘥𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯.
//𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=𝘢𝘶𝘵𝘰, 𝘤𝘰𝘭_𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=0. 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘩𝘦𝘢𝘥𝘦𝘳𝘴, 𝘨𝘳𝘢𝘯𝘥 𝘢𝘯𝘥 𝘴𝘶𝘣𝘵𝘰𝘵𝘢𝘭𝘴 𝘤𝘰𝘭𝘶𝘮𝘯 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵.
=PIVOTBY(B1:B8, A1:A8, D1:D8, LAMBDA(a, IF(SUM(a)>=20000, "great", "bad")), , , , 0)
//𝘤𝘢𝘭𝘤𝘶𝘭𝘢𝘵𝘦𝘥 𝘸𝘪𝘵𝘩 𝘤𝘶𝘴𝘵𝘰𝘮 𝘭𝘢𝘮𝘣𝘥𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯.
//𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=𝘢𝘶𝘵𝘰, 𝘤𝘰𝘭_𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=0. 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘩𝘦𝘢𝘥𝘦𝘳𝘴, 𝘨𝘳𝘢𝘯𝘥 𝘢𝘯𝘥 𝘴𝘶𝘣𝘵𝘰𝘵𝘢𝘭𝘴 𝘤𝘰𝘭𝘶𝘮𝘯 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵.
=PIVOTBY(B1:B8, A1:A8, D1:D8, LAMBDA(a, IF(SUM(a)>=20000, "great", "bad")), , , , 0)

|   | A         | B    | C    | D     |
|---|-----------|------|------|-------|
| 1 |           | 2015 | 2016 | 2017  |
| 2 | Clothing  | bad  | bad  | great |
| 3 | Component | bad  |      | great |
| 4 | Total     | bad  | bad  | great |

EXAMPLE 3

Copied!//𝘢𝘱𝘱𝘭𝘺 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘭𝘢𝘮𝘣𝘥𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯𝘴 𝘵𝘰 𝘦𝘢𝘤𝘩 𝘨𝘳𝘰𝘶𝘱.
//𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=1, 𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=0. 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘩𝘦𝘢𝘥𝘦𝘳𝘴, 𝘨𝘳𝘢𝘯𝘥 𝘢𝘯𝘥 𝘴𝘶𝘣𝘵𝘰𝘵𝘢𝘭𝘴 𝘤𝘰𝘭𝘶𝘮𝘯 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵.
=PIVOTBY(B1:B8, A1:A8, D1:D8, HSTACK(SUM, AVERAGE, MAX), 1, , , 0)
//𝘢𝘱𝘱𝘭𝘺 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘭𝘢𝘮𝘣𝘥𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯𝘴 𝘵𝘰 𝘦𝘢𝘤𝘩 𝘨𝘳𝘰𝘶𝘱.
//𝘧𝘪𝘦𝘭𝘥_𝘩𝘦𝘢𝘥𝘦𝘳𝘴=1, 𝘵𝘰𝘵𝘢𝘭_𝘥𝘦𝘱𝘵𝘩=0. 𝘥𝘰𝘯'𝘵 𝘴𝘩𝘰𝘸 𝘩𝘦𝘢𝘥𝘦𝘳𝘴, 𝘨𝘳𝘢𝘯𝘥 𝘢𝘯𝘥 𝘴𝘶𝘣𝘵𝘰𝘵𝘢𝘭𝘴 𝘤𝘰𝘭𝘶𝘮𝘯 𝘪𝘯 𝘳𝘦𝘴𝘶𝘭𝘵.
=PIVOTBY(B1:B8, A1:A8, D1:D8, HSTACK(SUM, AVERAGE, MAX), 1, , , 0)

|   | A         |   B   |      C      |   D   |  E   |    F    |  G   |   H   |    I    |   J   |
|---|-----------|-------|-------------|-------|------|---------|------|-------|---------|-------|
| 1 |           | 2015  |    2015     | 2015  | 2016 |  2016   | 2016 | 2017  |  2017   | 2017  |
| 2 |           |  SUM  |   AVERAGE   |  MAX  | SUM  | AVERAGE | MAX  |  SUM  | AVERAGE |  MAX  |
| 3 | Clothing  | 17000 |    8500     | 13300 | 2300 |  2300   | 2300 | 40000 |  20000  | 36000 |
| 4 | Component | 2300  |    2300     | 2300  |      |         |      | 20000 |  20000  | 20000 |
| 5 | Total     | 19300 | 6433.333333 | 13300 | 2300 |  2300   | 2300 | 60000 |  20000  | 36000 |

RELATED