How to Use Multiple LAMBDA Functions in Excel GROUPBY

A user on the Microsoft Tech Community recently asked: “I am loving the new GROUPBY function, but I am stuck. I want to show the Sum, Average, and a custom Percent-of-Total calculation all in one formula. Can I pass multiple LAMBDA functions into the ‘function’ argument, or am I restricted to just one?”

This is a common hurdle when moving from traditional Pivot Tables to the new “Dynamic Array” way of working. The answer is yes, you can use multiple functions, but you have to package them correctly using HSTACK.

The Minimal Working Example

To use multiple aggregations (including custom LAMBDAs) within GROUPBY, you must use the HSTACK function to group your calculations into a single array argument.

The Formula:

=GROUPBY(A2:A20, B2:B20, HSTACK(SUM, AVERAGE, LAMBDA(x, MAX(x)-MIN(x))), 3, 0)

Tested on Microsoft Excel 365 (Version 2408) as of late 2024.

Why This Works

The GROUPBY function expects a single reference in its third argument (function). By using HSTACK, you are telling Excel to perform three separate calculations on the same data slice and “stack” them horizontally in the resulting table.

Argument	Purpose	Example Value
row_fields	The column you want to group by.	A2:A20 (e.g., Category)
values	The numeric data you want to aggregate.	B2:B20 (e.g., Revenue)
function	The math to perform.	HSTACK(SUM, AVERAGE, LAMBDA(...))
field_headers	Whether to show headers (0=No, 3=Yes).	3
total_depth	Whether to show Grand Totals.	0 (No Totals)

Adapting to Your Own Data

To apply this to your specific spreadsheet, follow these steps:

1. Define your Row Fields: Select the range containing the labels (e.g., Department names or Product IDs).
2. Define your Values: Select the range containing the numbers you want to crunch.
3. Construct the HSTACK:
   • For standard math, use the function name without parentheses (e.g., SUM, PERCENTOF).
   • For custom math, write a LAMBDA. For example, to find the “Range” of your data, use LAMBDA(x, MAX(x) - MIN(x)).
4. Enter the Formula: Type the formula in a single cell. Because it is a dynamic array, it will automatically “spill” to fill the neighboring cells.

Common Questions & Troubleshooting

What if I need different column headers for my LAMBDAs?

By default, custom LAMBDAs in GROUPBY might show a generic header like “unnamed”. To fix this, you can use the COLUMNVECTOR or VSTACK approach to create a header row manually, or wrap your GROUPBY in a LET function to define names. However, the easiest way is to ensure field_headers is set to 3 and that your source data has clean headers.

Can I apply this to filtered rows only?

GROUPBY does not automatically ignore hidden rows. If you want to aggregate only filtered data, you should wrap your values argument in a FILTER function. Example: GROUPBY(A2:A20, FILTER(B2:B20, C2:C20="Active"), HSTACK(SUM, AVERAGE))

Does this work with PIVOTBY?

Yes! The logic is identical. If you are using PIVOTBY to create a matrix with both rows and columns, the function argument behaves exactly the same way. You can HSTACK multiple LAMBDAs to see multiple metrics for every intersection in your pivot.

Is there a limit to how many LAMBDAs I can stack?

While there is no hard-coded limit to the number of functions in an HSTACK, remember that Excel’s calculation engine must process each one for every unique group. If you have 100,000 rows and 15 custom LAMBDAs, you may notice a slight calculation lag when data changes. For most standard business reports, 3–5 functions will perform instantly.