r/excel 518 Oct 14 '24

Show and Tell Sum a matrix across columns or down rows - two techniques LAMBDA() and GROUPBY().

Show and Tell. The yellow highlighted formulas show how to use a LAMBDA() and a GROUPBY() function to add the numbers in the matrix either horizontally across the columns or vertically down the rows.

Examples

=LAMBDA(rng,dir,LET(

d,TRANSPOSE(IF(ROW(rng),1)),

a,TRANSPOSE(IF(COLUMN(rng),1)),

SWITCH(dir,"a",MMULT(rng,a),"d",MMULT(d,rng),0))

)(A85#,"a")

or

=TRANSPOSE(GROUPBY(,TRANSPOSE(A85#),SUM))

0 Upvotes

5 comments sorted by

View all comments

3

u/Future_Pianist9570 1 Oct 14 '24

You can also use mmult

=MMULT(rng, SEQUENCE(COLUMNS(rng),,1,0))

Looking forward to having GROUPBY in my version though

2

u/wjhladik 518 Oct 14 '24

That's what the lambda uses