excel-lambda-GROUPAGGREGATE: Quickly create summary tables in Excel with just one formula

This is how it works:

GROUPAGGREGATE takes two parameters:

1. dat – a range of data you want to summarize. In the gif above, I’ve selected some columns from my query
2. control – an array of values with one row and the same number of columns as dat, where the values indicate what you want to do with each column in dat

You define an array of control values, which tell the function what to do with each column, then you pass the data into the first parameter and the control array into the second parameter. With the code above, you can use one of these values in the control array (which I have on row 1 in the gif above):

If you’re feeling brave, you can always extend the list of aggregates supported by modifying the LAMBDA for GROUPAGGREGATE.

If you’d like to use this function, you will need to grab the code for the RECURSIVEFILTER function from this page and define it as Named formula in your Name Manager in your workbook, then define GROUPAGGREGATE using the code above.

If you want to understand how GROUPAGGREGATE works, read on. Fair warning – it might get quite involved!

How it works

First of all, we create 6 variables:

Now that these variables are assigned, we can do the tricky work of building the output array.

We’ll create a variable called result_arr and we’ll use MAKEARRAY to populate it. This is going to have the same number of rows as groups and the same number of columns as dat.

To populate the array, we use a LAMBDA function which has two parameters – r and c, representing the row and column position in the array.

First, we define a variable called measure_col. This will get the index from val_col_indices at the position (c – group_cols).

If we have two groups, then group_cols is 2.

If the output column c is 3 (i.e. the first non-group output column because there are 2 groups), then (c – group_cols) = 3 – 2 = 1 and we take the 1st item from val_col_indices.

val_col_indices is {1,3,5,6}, so the first item is 1. Column 1 in dat is country_dependency, and it’s corresponding control value is “textjoin”.

MAKEARRAY(
		ROWS(groups),
		COLUMNS(dat),
		LAMBDA(r,c,
				LET(measure_col,INDEX(val_col_indices,1,c-group_cols),

Next, we define the measure. This is returning the column at position measure_col from the result of the displayed call to RECURSIVEFILTER. The details of what RECURSIVEFILTER does exactly can be found here. It’s a little complex, very powerful and well worth reading about.

For the sake of this example, we are filtering dat (which is the dataset passed into the function) by the column numbers in group_col_indices (in our example, {2,4}) and the values in INDEX(groups,r). In the example above, that’s {“Asia”,”National annual estimate”}.

MAKEARRAY(
		ROWS(groups),
		COLUMNS(dat),
		LAMBDA(r,c,
				LET(measure_col,INDEX(val_col_indices,1,c-group_cols),
					measure,INDEX(
								RECURSIVEFILTER(dat,
												group_col_indices,
												INDEX(groups,r)
												)
								,,measure_col
								),

RECURSIVEFILTER will return a filtered subset of the main table which has the same values in the group columns as the current row of the output array.

On the first row of the output array, this is region=”Asia” and source=”National annual estimate”. So we grab all the rows from the main table with those values in those columns.

From that filtered subset of rows, we are then returning the column with index measure_col into the variable measure.

At this point, measure is just a single column of data for only those rows which match the filter (i.e. the row header of the output) and for only that column that needs to be aggregated.

Once we have that data, we can apply any kind of aggregation to it that we want.

Using RECURSIVEFILTER to get to an array of data to aggregate lets us side-step the need to use positional lists of parameters in functions like SUMIFS, MAXIFS, MINIFS and so on. We simply pass a column from RECURSIVEFILTER into SUM, MAX or MIN (or whatever other function we want to use)

The remainder of the function is saying: if the column number c is less than or equal to group_cols, then place the value from the groups variable on the row header.

If the column number c is greater than group_cols, then we know we need to place some aggregate of measure in the output array in this column.

We first get the control value for this column using INDEX(control,1,measure_col). For the input column country_dependency, the control value is “textjoin”, and we match it against the typed array shown below.

XMATCH(
		INDEX(control,1,measure_col),
		{"textjoin",
		"sum",
		"min",
		"max",
		"counta",
		"count",
		"average"}
		),

We find “textjoin” in the first position. We then take the first option from the list of options in the CHOOSE function.

CHOOSE(
		XMATCH(
				INDEX(control,1,measure_col),
				{"textjoin",
				"sum",
				"min",
				"max",
				"counta",
				"count",
				"average"}
				),
		TEXTJOIN(", ",FALSE,SORT(UNIQUE(measure))),
		SUM(measure),
		MIN(measure),
		MAX(measure),
		COUNTA(measure),
		COUNT(measure),
		AVERAGE(measure)
		)

The first option is TEXTJOIN(…etc. This function is then applied to measure and the result becomes the value for row r and column c in the output array of GROUPAGGREGATE.

The beauty of MAKEARRAY is that it will repeat the process above for each value of r and c. So, we will use control to apply the correct method – group the column, or aggregate the column – to a filtered list of data from the main data table.

The result of all this is we create a summary table with one simple formula. And the nice thing is, we can easily just change the control array and watch as the summary table updates dynamically:

There may be a better way to do what I’ve done here. If there is, I want to hear about it! Regardless, this was a very useful learning exercise. I hope you’ve found this interesting and if you end up using either RECURSIVEFILTER or GROUPAGGREGATE, then all the better.