PIVOTBY function
Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify.
Lookup and reference · Microsoft 365 · Guided Excel example
Where can you use it?
Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.
Scenario in the workbook
Summarize amounts by region on rows and status on columns. Use an empty spill area.
A worked example from the workbook
| Row | A | B | C |
|---|---|---|---|
| 6 | Input | Second input | Third input |
| 7 | North | Open | 12 |
| 8 | South | Open | 8 |
| 9 | North | Closed | 5 |
Record 1
=PIVOTBY(A7:A9,B7:B9,C7:C9,SUM)Illustrative outputNorth total 17
See the inputs and instructions for all 3 examplesWorked examples
Summarize amounts by region on rows and status on columns. Use an empty spill area.
Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.
| Row | A | B | C |
|---|---|---|---|
| 6 | Input | Second input | Third input |
| 7 | North | Open | 12 |
| 8 | South | Open | 8 |
| 9 | North | Closed | 5 |
| Trial | Expression | Sample output |
|---|---|---|
| Record 1 | =PIVOTBY(A7:A9,B7:B9,C7:C9,SUM) |
North total 17 |
| Record 2 | =PIVOTBY(A7:A9,B7:B9,C7:C9,SUM) |
South total 8 |
| Record 3 | =PIVOTBY(A7:A9,B7:B9,C7:C9,SUM) |
Grand total 25 |