## Summary

The Excel PIVOTBY function is designed to summarize data by grouping rows and columns. The result is a dynamic summary table created with a single formula.

## Purpose

Summarize data by grouping rows and columns

## Return value

Data summary table

## Syntax

``=PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array])``
• row_fields - The values for grouping rows.
• col_fields - The values for grouping columns.
• values - The values to aggregate.
• function - The calculation to run when aggregating.
• field_headers - [optional] 0 = No, 1 = Yes, don't show, 2 = No, generate, 3 = Yes, show.
• row_total_depth - [optional] 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
• row_sort_order - [optional] Row to sort by as number. Negative numbers = descending order.
• col_total_depth - [optional] 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
• col_sort_order - [optional] Column to sort by as number. Negative numbers = descending order.
• filter_array - [optional] Logic to remove specific rows.

## How to use

The PIVOTBY function is designed to summarize data by grouping rows and columns. The result is a dynamic summary table created with a single formula. The result from the PIVOTBY function is similar to the output from a Pivot Table but without formatting. The table returned by the PIVOTBY function is fully dynamic and will immediately recalculate when source data changes.

The PIVOTBY function is similar to the GROUPBY function. The difference is that PIVOTBY can group by row and column, whereas GROUPBY can group by row only.

### Basic Example

The PIVOTBY function takes ten arguments but only the first four are required. In the worksheet shown below, we use the PIVOTBY function to summarize sales by city and year. The formula in cell F5 is:

``=PIVOTBY(B5:B28,C5:C28,E5:E28,SUM)``

For this example, PIVOTBY is configured with three arguments as follows:

• row_fields - provided as the range B5:B16, which contains city names.
• col_fields - provided as the range C5:C16, which contains years.
• values - provided as the range E5:E16, which contains sales amounts.
• function - provided as SUM, the calculation to perform during aggregation.

With the inputs above, the PIVOTBY function summarizes sales by city in rows and sales by year in columns. The entire table in G4:J8 is returned in one step. Notice that we have not included the header row in row_fields, col_fields, or values, because we are not asking PIVOTBY to generate a header automatically in this case. Note also that PIVOTBY includes a Total row and Total column by default.

Author

### Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.