This Excel LAMBDA function creates summary tables similar to SQL GROUP BY queries

excel-lambda-GROUPAGGREGATE.txt

/*
GROUPAGGREGATE

Creates summary tables of data similar to SQL's GROUP BY queries

Inputs
	- dat: 	a range or array of data with at least two columns, 
			one of which's control value must be "group" and one not "group"
	- control: a single-row array, where COLUMNS(control)=COLUMNS(dat), of values from this list:
		group - the values in this column will be output as row headers
		textjoin - the values in the corresponding column in dat will be passed to the TEXTJOIN function
		sum - the values in the corresponding column in dat will be passed to the SUM function
		min - the values in the corresponding column in dat will be passed to the MIN function
		max - the values in the corresponding column in dat will be passed to the MAX function
		counta - the values in the corresponding column in dat will be passed to the COUNTA function
		count - the values in the corresponding column in dat will be passed to the COUNT function
		average - the values in the corresponding column in dat will be passed to the AVERAGE function
	
Returns
	An array with COLUMNS(dat) columns and ROWS([unique combinations of grouping values]) rows, 
	where each non-group cell on a row is the result of the corresponding control function applied 
	to that column for those groups
	
Dependencies
	RECURSIVEFILTER
	https://gist.github.com/ncalm/f1c7b5bd8fe86528da88798b545053c8

*/


GROUPAGGREGATE = LAMBDA(dat,control,
          LET(
            group_control,control="group",
            group_dat,FILTER(dat,group_control),
            groups,UNIQUE(group_dat),
            group_cols,COLUMNS(groups),
            group_col_indices,LET(f,SEQUENCE(1,COUNTA(control))*(group_control),FILTER(f,f<>0)),
            val_col_indices,LET(f,SEQUENCE(1,COUNTA(control))*(group_control=FALSE),FILTER(f,f<>0)),
            result_arr,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),
                        IF(
                          c<=group_cols,
                          INDEX(groups,r,c),
                          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)
                          )
                        )
                      )
                    )
                  ),
            result_arr
          )
);

This is an absolutely beautiful piece of work. I will be using it heavily until Group by is rolled out!

@ExcelRobot , can you not use choosecolumns to grab the cols you want?(for suggestion 1)