Running totals in 2D Dynamic array

[vampsthevampyre]

Evening All,

I'm looking to create a dynamic 2D array that generates a running total (L4-P13) for each of the possible numbers (F4-F8).

Any help would be appreciated

Regards
Ian

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
2
3		Possible Numbers		ID	Numbers			1	2	3	4	5
4		1		0	0			0	0	0	0	0
5		2		1	1			1	0	0	0	0
6		3		2	2			1	1	0	0	0
7		4		3	3			1	1	1	0	0
8		5		4	4			1	1	1	1	0
9				5	5			1	1	1	1	1
10				6	2			1	2	1	1	1
11				7	4			1	2	1	2	1
12				8	3			1	2	2	2	1
13				9	1			2	2	2	2	1
14
Sheet6

Cell Formulas
Range	Formula
L3:P3	L3	=TRANSPOSE(Table12[Possible Numbers])
H4:H13	H4	=ROW()-ROW(Table11[#Headers])-1
Dynamic array formulas.

 

[Fluff]

How about

=DROP(REDUCE("",L3#,LAMBDA(x,y,HSTACK(x,SCAN(0,Table11[Numbers],LAMBDA(a,b,IF(b=y,a+1,a)))))),,1)

 

[vampsthevampyre]

How about

=DROP(REDUCE("",L3#,LAMBDA(x,y,HSTACK(x,SCAN(0,Table11[Numbers],LAMBDA(a,b,IF(b=y,a+1,a)))))),,1)

Amazing thanks for the solution

 

[Fluff]

You're welcome & thanks for the feedback.

 

[djclements]

For demonstration purposes, it's also possible to SCAN by column without using REDUCE-HSTACK:

=LET(
    arr, EXPAND(Table12[Possible Numbers]=TOROW(Table11[Numbers]),,ROWS(Table11)+1),
    DROP(TRANSPOSE(SCAN(0,arr,LAMBDA(a,v,IFNA(a+v,0)))),-1)
)

Since SCAN works in row major order, start by flipping the array on its axis so the columns will be horizontal. Then, EXPAND the new array by one column, to be used as the reset point at the end of each row (when the [pad_with] argument is omitted, the new column will be filled with #N/A errors, which can be trapped with IFNA). Lastly, TRANSPOSE the results and DROP the additional row.