ExcelFlux

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

RowABC
6InputSecond inputThird input
7NorthOpen12
8SouthOpen8
9NorthClosed5
Record 1
=PIVOTBY(A7:A9,B7:B9,C7:C9,SUM)

Illustrative outputNorth total 17

See the inputs and instructions for all 3 examples

Worked 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
Microsoft Excel reference