Excel SCAN function by for each column in a table

[jrhouse5]

Can the Excel SCAN function work on a table a calculate a running total for each column individually. I tried the following formula:

=TRANSPOSE(SCAN(0,TRANSPOSE(Table_Array),LAMBDA(a,v,a+v)))

However, I get a running cum by column for the whole table, not for each column individually.

 

[duggie33]

Hi jrhouse5,

Please post your data using the XL2BB tool so we can copy and paste your data locally and work on a solution.

Thanks,

Doug

 

[jrhouse5]

will do next time. however, i have found a solution.

 

[jrhouse5]

the above is actually by columns!

 

[duggie33]

Nice, MAKEARRAY, another function I have not used and should probably learn.

Thanks,

Doug

 

[Fluff]

You could also do that with mmult like

=LET(r,ROWS(Table_Array),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),--Table_Array))

 

[Fluff]

If you want to use a lambda function, you could also shorten your formula slightly like

=MAKEARRAY(ROWS(Table_Array),COLUMNS(Table_Array),LAMBDA(r,c,SUM(INDEX(Table_Array,SEQUENCE(r),c))))