Dynamically sum multiple columns, based on grouped criteria from another array

[PrettyGood_Not Great]

Hi,

I have a challenge that I am not sure exactly how to describe. The mini sheet below shows a data set that we want to SUM based on criteria. The criteria for that SUM has two elements. Element A, the headers of the data set and Element B, the headers of the return array. Element B represents a grouping of Element A and the groupings are defined in the Criteria array.

To the far right are the results if we hard code a simple addition formula. I am search for a dynamic array formula that can spill down, if possible.

I have tried using FILTER(UNIQUE()) to produce a grouping of Element A based on the Element B and tried using that as the criteria within an XLOOKUP, however I only get the first result, and this approach may be flawed to begin with, not clear.

SORI Labour_Report_M365_v1.0.4.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Criteria Array:	Group	Value	Data Array:	10	20	30	40	50	60	70	80	Return Array	A	B	C	Expected Result	A	B	C
2		A	10		0.391814			0.133059					(dynamic solution)					0.391814	0	0
3		B	20		0.568253			0.085723		0.610849								1.179102	0	0
4		C	30		0.216626		0.30197	0.899583		0.203058								0.419684	0	0.30197
5		D	40		0.723318		0.706881	0.898594		0.591953								1.315272	0	0.706881
6		E	50		0.618591		0.544304	0.193965		0.666306								1.284897	0	0.544304
7		A	60		0.499179		0.102664	0.179014		0.636041	0.919326							1.13522	0.919326	0.102664
8		B	70		0.87141		0.237927	0.023436		0.238309	0.996059							1.10972	0.996059	0.237927
9		C	80		0.374391		0.48981	0.768663		0.43609	0.491172							0.81048	0.491172	0.48981
10					0.428265			0.062302	0.020821	0.285586	0.864342							0.713851	0.864342	0
11					0.213887			0.759972	0.885522	0.999626								1.213513	0	0
12						0.675298		0.085273	0.301663	0.29996		0.883859						0.29996	0.675298	0.883859
13						0.457291		0.109831	0.755208	0.377833		0.772912						0.377833	0.457291	0.772912
14						0.424749			0.57138	0.948473		0.277651						0.948473	0.424749	0.277651
15						0.052585			0.356184			0.939008						0	0.052585	0.939008
16						0.645729			0.151715									0	0.645729	0
17						0.359257			0.012225									0	0.359257	0
18						0.425812			0.058185									0	0.425812	0
19						0.11605			0.635948									0	0.11605	0
Sheet3

Cell Formulas
Range	Formula
R2:T19	R2	=E2+J2
G4:G9,I10:I19,F12:F19,L12:L15,J3:J14,H2:H13,E2:E11,K7:K10	G4	=RAND()

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Criteria Array:	Group	Value	Data Array:	10	20	30	40	50	60	70	80	Return Array	A	B	C
2		A	10		0.626			0.484					(dynamic solution)	0.626	0.000	0.000
3		B	20		0.145			0.158		0.598				0.743	0.000	0.000
4		C	30		0.940		0.976	0.477		0.046				0.986	0.000	0.976
5		D	40		0.243		0.839	0.289		0.831				1.074	0.000	0.839
6		E	50		0.661		0.600	0.096		0.867				1.528	0.000	0.600
7		A	60		0.004		0.479	0.276		0.033	0.729			0.037	0.729	0.479
8		B	70		0.715		0.056	0.522		0.264	0.880			0.978	0.880	0.056
9		C	80		0.332		0.746	0.888		0.161	0.491			0.494	0.491	0.746
10					0.131			0.714	0.153	0.936	0.166			1.067	0.166	0.000
11					0.373			0.406	0.669	0.341				0.713	0.000	0.000
12						0.268		0.270	0.963	0.605		0.799		0.605	0.268	0.799
13						0.300		0.619	0.981	0.592		0.623		0.592	0.300	0.623
14						0.436			0.450	0.828		0.870		0.828	0.436	0.870
15						0.261			0.888			0.904		0.000	0.261	0.904
16						0.912			0.506					0.000	0.912	0.000
17						0.159			0.347					0.000	0.159	0.000
18						0.536			0.493					0.000	0.536	0.000
19						0.698			0.657					0.000	0.698	0.000
Data

Cell Formulas
Range	Formula
N2:P19	N2	=BYROW($E$2:$L$19,LAMBDA(br,SUM(CHOOSECOLS(br,XMATCH(FILTER($C$2:$C$9,$B$2:$B$9=N1),$E$1:$L$1)))))
Dynamic array formulas.

 

[PrettyGood_Not Great]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Criteria Array:	Group	Value	Data Array:	10	20	30	40	50	60	70	80	Return Array	A	B	C
2		A	10		0.626			0.484					(dynamic solution)	0.626	0.000	0.000
3		B	20		0.145			0.158		0.598				0.743	0.000	0.000
4		C	30		0.940		0.976	0.477		0.046				0.986	0.000	0.976
5		D	40		0.243		0.839	0.289		0.831				1.074	0.000	0.839
6		E	50		0.661		0.600	0.096		0.867				1.528	0.000	0.600
7		A	60		0.004		0.479	0.276		0.033	0.729			0.037	0.729	0.479
8		B	70		0.715		0.056	0.522		0.264	0.880			0.978	0.880	0.056
9		C	80		0.332		0.746	0.888		0.161	0.491			0.494	0.491	0.746
10					0.131			0.714	0.153	0.936	0.166			1.067	0.166	0.000
11					0.373			0.406	0.669	0.341				0.713	0.000	0.000
12						0.268		0.270	0.963	0.605		0.799		0.605	0.268	0.799
13						0.300		0.619	0.981	0.592		0.623		0.592	0.300	0.623
14						0.436			0.450	0.828		0.870		0.828	0.436	0.870
15						0.261			0.888			0.904		0.000	0.261	0.904
16						0.912			0.506					0.000	0.912	0.000
17						0.159			0.347					0.000	0.159	0.000
18						0.536			0.493					0.000	0.536	0.000
19						0.698			0.657					0.000	0.698	0.000
Data

Cell Formulas
Range	Formula
N2:P19	N2	=BYROW($E$2:$L$19,LAMBDA(br,SUM(CHOOSECOLS(br,XMATCH(FILTER($C$2:$C$9,$B$2:$B$9=N1),$E$1:$L$1)))))
Dynamic array formulas.

Just another day for @Fluff. WOW! that's perfect, thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.