ID EN
…
#Dynamic array

PIVOTBY

Excel Functions

Summarize data by grouping rows and columns

Syntax

EXCEL 
=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])

Arguments

Parameter Description
row_fields The values to use when grouping rows.
col_fields The values to use when 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] Enable or disable row totals. 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
row_sort_order [optional] A column number indicating how rows should be sorted. Negative numbers = descending order.
col_total_depth [optional] Enable or disable column totals. 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
col_sort_order [optional] A column number indicating how columns should be sorted. Negative numbers = descending order.
filter_array [optional] Logic to exclude specific rows.
relative_to [optional] Where to find values for the 2nd argument of a function. 0 = column totals (default), 1 = Row Totals, 2 = Grand Totals, 3 = Parent Col Total, 4 = Parent Row Total.

Return Value

Data summary table

Details

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. Here is a brief list of PIVOTBY features and limitations: The PIVOTBY function takes 11 arguments, but only the first four are required. In the worksheet below, we use the PIVOTBY function to summarize sales by city and year. The formula in cell F5 is: For this example, PIVOTBY is configured with three arguments as follows: With the inputs above, the PIVOTBY function sums sales by city in rows and sales by year in columns

Examples

Basic Example

The PIVOTBY function takes 11 arguments, but only the first four are required. In the worksheet below, we use the PIVOTBY function to summarize sales

EXCEL 
=PIVOTBY(B5:B28,C5:C28,E5:E28,SUM)
PIVOTBY rows vs. columns

Compared to the GROUPBY function (which groups only by row) the main benefit of PIVOTBY is the ability to group by row and by column. This behavior is

EXCEL 
=PIVOTBY(B4:B28,C4:C28,E4:E28,SUM)
PIVOTBY rows vs. columns

In the second table, we swap rows and columns. Now we group rows by Year and columns by City:

EXCEL 
=PIVOTBY(C4:C28,B4:B28,E4:E28,SUM)
PIVOTBY with field headers

The field_header argument is optional. When field_header is omitted, PIVOTBY will try to detect headers in the source data automatically by testing va

EXCEL 
PIVOTBY(B4:B28,,E4:E28,SUM,3) // display field headers
PIVOTBY calculation options

In the worksheet below, we have a list of meal preferences for 100 employees in different departments and the cost per meal. We can use the PIVOTBY fu

EXCEL 
=PIVOTBY(D4:D104,,D4:D104,COUNTA,1)
PIVOTBY calculation options

Next, let's change the calculation to generate a total cost by meal, by using the SUM function instead of COUNTA. In the worksheet below, the formula

EXCEL 
=PIVOTBY(D4:D104,,E4:E104,SUM,1)

See Also

GROUPBY HSTACK VSTACK PERCENTOF