9. Dynamic Arrays

9.10 GROUPBY: A Summary Table with One Formula

By Ravindra Bagale · Last updated: 6 October 2026

Example first. Shraddha has the 10 fictional orders in Orders!A1:I11 (City in column C, Amount in column G). Her manager asks: "How much did each city sell?" Before GROUPBY she would build a PivotTable or write UNIQUE plus SUMIFS. Now she types one formula in K2 and Excel spills a sorted list of cities, their totals and a Total row. When an order changes, the summary changes by itself. No Refresh button.

First look at the data (before → after):

Expected result — K2 spills (sorted A to Z, Total row added)

K2: =GROUPBY(C2:C11,G2:G11,SUM)
row_fields = C2:C11 (City)   values = G2:G11 (Amount)   function = SUM

The syntax in plain English

GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

Here are six more real examples on the same 10 orders. Type each one in an empty cell with space around it.

Example 2: show the header row. Select the headers too and set field_headers to 3.

=GROUPBY(C1:C11,G1:G11,SUM,3)

Example 3: biggest city first. City is column 1 and Amount is column 2, so -2 sorts by Amount, largest first.

=GROUPBY(C2:C11,G2:G11,SUM,,,-2)

The two empty commas skip field_headers and total_depth. The Total row stays at the bottom.

Example 4: only Delivered orders. The filter_array keeps only rows where Status is Delivered.

=GROUPBY(C2:C11,G2:G11,SUM,,,,I2:I11="Delivered")

Nagpur (only a cancelled order) and Sambhaji Nagar (only a returned order) disappear because none of their rows pass the filter.

Example 5: count orders per city. Use COUNT on the Amount column (COUNT counts numbers).

=GROUPBY(C2:C11,G2:G11,COUNT)

Example 6: two value columns at once. Qty (F) and Amount (G) sit next to each other, so give F2:G11 as values.

=GROUPBY(C2:C11,F2:G11,SUM)

Example 7: share of sales with PERCENTOF. Format the result column as Percentage (Home › Number › %).

=GROUPBY(C2:C11,G2:G11,PERCENTOF)

Example 8: two levels (City, then Platform), grand total only. C and D are next to each other, so C2:D11 gives two row fields. total_depth 1 shows only the grand total.

=GROUPBY(C2:D11,G2:G11,SUM,,1)

With total_depth 2 (or left empty) Excel also adds a subtotal row under each city that has more than one platform.

The old way (works in every version)

Common problems

How these results were checked: this function is not available in LibreOffice, so every expected value on this page was computed in Python and cross-checked in LibreOffice with classic formulas that give the same numbers. The layout of the spill follows Microsoft's documentation. Microsoft reference: GROUPBY function.

Practice task (try it before you open the answer):

1. Use GROUPBY to show the total Amount per Category (column E) on the 10 orders.
2. Sort the result so the category with the largest Amount is first.
3. Then show only Blinkit orders (Platform in column D) in the same summary.