Get a 2D spilled formula to fulfill a distribution table matching the ranges of both dimensions

[ED38]

Hello,

I have found different way to fulfill my table but none of them are manageable and future proof in the context of my file, so I am trying to get a 2D spilled formula in cell G5 that will fulfill the table without refering to intermadiate calculation in other cells (like I did in the mini sheet below).
when I try to merge the cell N5 and O5 into G5, it does not work (nested array, I guess)
Would you one of you have a solution for this?

Thanks a lot for your help.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1					DISERED DISTRIBUTION TABLE													Initial attempt (not spilled formula)
2							1	2	3	4	5	6		Intermediate Spilled Formulas
3	DATA SET (64)	64			Qty range	% range	0%	<20%	<40%	<60%	<80%	<100%						0%	<20%	<40%	<60%	<80%	<100%
4	Qty	%		1	0									Qty rank	% rank
5	220	37,5%		2	11		15	18	9	4	-	-		7	2				15	18	9	4	-
6	220	37,5%		3	26		2	3	3	-	-	-		7	2				2	3	3	-	-
7	100	39,3%		4	51		1	1	-	2	-	-		5	2				1	1	-	2	-
8	100	39,3%		5	81		-	-	-	-	-	-		5	2				-	-	-	-	-
9	43	39,0%		6	101		-	3	-	-	-	-		3	2				-	3	-	-	-
10	5	44,0%		7	126		-	-	-	-	-	-		1	3				-	-	-	-	-
11	15	43,6%		8	252		-	2	1	-	-	-		2	3				-	2	1	-	-
12	11	67,0%		9	501		-	-	-	-	-	-		1	4				-	-	-	-	-
13	20	36,4%		10	751		-	-	-	-	-	-		2	2				-	-	-	-	-
14	20	41,8%		11	1501		-	-	-	-	-	-		2	3				-	-	-	-	-
15	8	20,0%		12	2001		-	-	-	-	-	-		1	2				-	-	-	-	-
16	11	73,0%		13	2501		-	-	-	-	-	-		1	4				-	-	-	-	-
17	8	21,0%		14	3001		-	-	-	-	-	-		1	2				-	-	-	-	-
18	13	18,2%			99999999		-	-	-	-	-	-		2	1				-	-	-	-	-
19	15	29,1%												2	2
20	90	35,7%												5	2
21	5	0,0%				Total	18	27	13	6	0	0	64	1	1
22	11	58,0%												1	3
23	160	43,8%												7	3
24	11	60,0%												1	4
25	5	17,0%												1	1
26	5	17,0%												1	1
27	11	64,0%												1	4
28	34	17,1%												3	1
29	5	33,0%												1	2
30	5	41,0%												1	3
31	5	39,0%												1	2
32	20	14,5%												2	1
33	10	13,0%												1	1
34	10	20,0%												1	2
35	1	20,0%												1	2
36	1	20,0%												1	2
37	8	26,0%												1	2
38	2	26,0%												1	2
39	51	65,9%												3	4
40	51	65,9%												3	4
41	20	34,5%												2	2
42	20	40,0%												2	3
43	3	36,0%												1	2
44	3	0,0%												1	1
45	3	0,0%												1	1
46	9	40,0%												1	3
47	7	19,0%												1	1
48	2	30,0%												1	2
49	5	30,0%												1	2
50	4	30,0%												1	2
51	1	41,0%												1	3
52	3	42,0%												1	3
53	2	18,0%												1	1
54	2	18,0%												1	1
55	1	30,0%												1	2
56	2	31,0%												1	2
57	1	40,0%												1	3
58	2	43,0%												1	3
59	2	44,0%												1	3
60	1	7,0%												1	1
61	1	0,0%												1	1
62	3	31,0%												1	2
63	1	15,0%												1	1
64	1	17,0%												1	1
65	1	18,0%												1	1
66	1	15,0%												1	1
67	1	20,0%												1	2
68	1	30,0%												1	2
Sheet1

Cell Formulas
Range	Formula
G2:L2	G2	=COLUMN(OFFSET($G$3:$L$3,0,0,6,))-COLUMN($G$3)+1
D4:D17	D4	=ROW(OFFSET(E4:E17,0,0,14,))-ROW($E$4)+1
G5:L18	G5	=COUNTIFS(O5#,"="&$G$2:$L$2,N5#,"="&$D$4:$D$17)
N5:N68	N5	=MATCH($A$5:$A$68,$E$4:$E$17+1,1)
O5:O68	O5	=MATCH($B$5:$B$68,$G$3:$L$3-0.0000001,1)
S5:W18	S5	=COUNTIFS(OFFSET($A$5,0,0,$B$3,),"<="&$E5,OFFSET($B$5,0,0,$B$3,),"<"&S$3)-SUM($R5:R5)-SUM($R$4:S4)
G21:L21	G21	=SUM(G5:G18)
M21	M21	=SUM(G21:L21)
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1					DISERED DISTRIBUTION TABLE
2							1	2	3	4	5	6
3	DATA SET (64)	64			Qty range	% range	0	0.2	0.4	0.6	0.8	1
4	Qty	%		1	0
5	220	0.375		2	11		15	18	9	4	0	0
6	220	0.375		3	26		2	3	3	0	0	0
7	100	0.39285714		4	51		1	1	0	2	0	0
8	100	0.39285714		5	81		0	0	0	0	0	0
9	43	0.3902439		6	101		0	3	0	0	0	0
10	5	0.44		7	126		0	0	0	0	0	0
11	15	0.43636364		8	252		0	2	1	0	0	0
12	11	0.67		9	501		0	0	0	0	0	0
13	20	0.36363636		10	751		0	0	0	0	0	0
14	20	0.41818182		11	1501		0	0	0	0	0	0
15	8	0.2		12	2001		0	0	0	0	0	0
16	11	0.73		13	2501		0	0	0	0	0	0
17	8	0.21		14	3001		0	0	0	0	0	0
18	13	0.18181818			99999999		0	0	0	0	0	0
19	15	0.29090909
20	90	0.35714286
21	5	0				Total	18	27	13	6	0	0	64
22	11	0.58
23	160	0.4375
24	11	0.6
25	5	0.17
26	5	0.17
27	11	0.64
28	34	0.17073171
29	5	0.33
30	5	0.41
31	5	0.39
32	20	0.14545455
33	10	0.13
34	10	0.2
35	1	0.2
36	1	0.2
37	8	0.26
38	2	0.26
39	51	0.65853659
40	51	0.65853659
41	20	0.34545455
42	20	0.4
43	3	0.36
44	3	0
45	3	0
46	9	0.4
47	7	0.19
48	2	0.3
49	5	0.3
50	4	0.3
51	1	0.41
52	3	0.42
53	2	0.18
54	2	0.18
55	1	0.3
56	2	0.31
57	1	0.4
58	2	0.43
59	2	0.44
60	1	0.07
61	1	0
62	3	0.31
63	1	0.15
64	1	0.17
65	1	0.18
66	1	0.15
67	1	0.2
68	1	0.3
Sheet6

Cell Formulas
Range	Formula
G2:L2	G2	=SEQUENCE(,COLUMNS($G$3:$L$3))
D4:D17	D4	=SEQUENCE(ROWS(E4:E17))
G5:L18	G5	=LET(a,XMATCH(A5:A68,E4:E17+1,-1),b,XMATCH(B5:B68,G3:L3-0.0000001,-1),MAKEARRAY(ROWS(D4#),COLUMNS(G2#),LAMBDA(r,c,SUM((a=r)*(b=c)))))
G21:L21	G21	=SUM(G5:G18)
M21	M21	=SUM(G21:L21)
Dynamic array formulas.

 

[ED38]

Thanks a lot Fluff, that answers the point and on top, as I wanted to avoid the ranges with the ranking (1,2 ,3,...) on both sides, the sequence formula your suggested allows to include that within a single formula I attached below as a final solution.

Great thanks to you

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1					DISERED DISTRIBUTION TABLE
2
3	DATA SET (64)	64			Qty range	% range	0%	20%	40%	60%	80%	100%
4	Qty	%			0
5	220	0,375			11		15	18	9	4	0	0
6	220	0,375			26		2	3	3	0	0	0
7	100	0,392857			51		1	1	0	2	0	0
8	100	0,392857			81		0	0	0	0	0	0
9	43	0,390244			101		0	3	0	0	0	0
10	5	0,44			126		0	0	0	0	0	0
11	15	0,436364			252		0	2	1	0	0	0
12	11	0,67			501		0	0	0	0	0	0
13	20	0,363636			751		0	0	0	0	0	0
14	20	0,418182			1501		0	0	0	0	0	0
15	8	0,2			2001		0	0	0	0	0	0
16	11	0,73			2501		0	0	0	0	0	0
17	8	0,21			3001		0	0	0	0	0	0
18	13	0,181818			99999999		0	0	0	0	0	0
19	15	0,290909
20	90	0,357143
21	5	0
Sheet2

Cell Formulas
Range	Formula
G5:L18	G5	=LET(a,XMATCH(A5:A68,E4:E17+1,-1),b,XMATCH(B5:B68,G3:L3-0.0000001,-1),MAKEARRAY(ROWS(SEQUENCE(ROWS($E$4:$E$17))),COLUMNS(SEQUENCE(,COLUMNS($G$3:$L$3))),LAMBDA(r,c,SUM((a=r)*(b=c)))))
Dynamic array formulas.

 

[Fluff]

Glad to help & thanks for the feedback.