Excel 2024: Create a Summary Table With the GROUPBY Function

The GROUPBY function debuted in late 2023. It makes it very easy to create a summary array. The optional arguments for the function provide flexibility.

The syntax of the function is:
=GROUPBY(row_fields,values,function,field_headers,[total_depth],[sort_order],[filter_array])

Let's start with a simple data set like this one:

Say that you want total sales for each category in column A. The Row Fields argument is A4:A21. The Values argument is C4:C21. There function is the word "Sum". A simple formula provides this summary:

In the next example, both the Sales and Costs column are the Values argument. The optional Headers argument is 3 for "Yes, and Show".

In the next example, the Row Fields are both Category and Product. The optional Total Depth argument now comes into play. A value of 2 says you want grand totals and subtotals as well. The optional Sort Order argument is -3. The 3 indicates that Excel should sort by the 3rd column of the array. The negative sign indicates that the values are sorted descending. The optional Filter Array removes any herbs from the results.

Check out the sorting closely. You've asked for the report to be sorted by descending sales. That means Vegetables with 104K in sales will be at the top. Within the Vegetables, Peppers are at the top with the largest sales.

---

---

Here are many details on the various arguments.

The third argument is called Function. You can put any LAMBDA function in this spot. The Excel team gave us 16 new Eta-Lambdas specifically for GROUPBY and PIVOTBY. Those functions are: SUM, PERCENTOF, AVERAGE, MEDIAN, COUNT, COUNTA, MAX, MIN, PRODUCT, ARRAYTOTEXT, CONCAT, STDEV.S, STDEV.P, VAR.S, VAR.P, and MODE.SNGL. These Eta-Lambdas are a shorthand way to simplify your formulas. Where you might have had to use LAMBDA(x,SUM(x)) in the past, you can now just specify SUM.

These Eta-Lambdas can be used inside of functions BYROW, BYCOL, SCAN, REDUCE, and MAKEARRAY.

The choices for the Field Headers argument are 0=No, 1=Yes But Don't Show, 2=No but generate, 3=Yes and Show.

The choices for Total Depth are 0=No Totals, 1=Grand Totals at Bottom, 2=Grand Totals and Subtotals at Bottom, -1=Grand Totals at Top, -2=Grand Totals and Subtotals at Top.

The Sort Order is the integer column number of the sort column. Positive 2 will sort by the second column in ascending sequence. Negative 2 will sort by the second column in descending sequence. You can sort by two columns with {3;-4}.

The Filter Array can be used to remove rows that match a criteria.

Bonus Tip: Counting with GROUPBY

The previous example was similar to using UNIQUE to get a list of items and then SUMIFS to total the columns. What if you essentially need to use COUNTIFS? This can happen easily with GROUPBY.

To count how many of each item appear in the data, you use the COUNTA function. In the following example, you are asking for every unique combination of columns A & B and then counting column B.