ASUMIFS

[Xlambda]

ASUMIFS !! recursive !! array sumifs alternative, that can handle array calculations as its arguments . calls T_IFS , APPEND2H
Other functions on minisheet: ADATE , ACOMBINE , ASELECT

=LAMBDA(f,w,v,
    LET(r,ROWS(w),n,COLUMNS(v),x,MMULT(T_IFS(f,w,),INDEX(v,,n)),
       IF(n=1,APPEND2H(x,v,2),APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),n-1)),x,))
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	example that justifies the use of ASUMIFS (SUMIFS can not be used bcs of range argument), (quarterly amd monthly report)																			quarterly report - single cell formula that uses only initial array, no helper arrays needed
2							step 1 (transform date clm)						step 2 (grouping criteria)				step 3(report)			please imagine equivalent formula using SUMIFS ? (without helper arrays, no way)
3	sample "within array"						=ADATE(A4:E33,"q",)						=ACOMBINE(G4#,{1,2,3})				=ASUMIFS(M4#,G4:I33,J4:K33)			=ASUMIFS(ACOMBINE(ADATE(A4:E33,"q",),{1,2,3}),ASELECT(ADATE(A4:E33,"q",),,,,3,),J4:K33)
4	11-02-21	A	x	8	32		2021-Q1	A	x	8	32		2021-Q1	A	x		15	173		15	173	single cell formula Z6:	=ASUMIFS(ACOMBINE(ADATE(A4:E33,"m",),{1,3}),ASELECT(ADATE(A4:E33,"m",),,,,,{1,3}),J4:K33)
5	05-07-21	B	y	3	24		2021-Q3	B	y	3	24		2021-Q1	A	y		0	0		0	0		=ACOMBINE(ADATE(A4:E33,"m",),{1,3})			monthly report for x,y - single cell formula
6	28-12-21	C	y	2	53		2021-Q4	C	y	2	53		2021-Q1	B	x		13	126		13	126		2021-01-Jan	x		16	168
7	17-01-21	C	x	9	27		2021-Q1	C	x	9	27		2021-Q1	B	y		0	0		0	0		2021-01-Jan	y		5	24
8	22-02-21	C	x	6	34		2021-Q1	C	x	6	34		2021-Q1	C	x		25	151		25	151		2021-02-Feb	x		22	109
9	02-10-21	B	y	3	17		2021-Q4	B	y	3	17		2021-Q1	C	y		5	24		5	24		2021-02-Feb	y		0	0
10	28-09-21	A	y	9	18		2021-Q3	A	y	9	18		2021-Q2	A	x		0	0		0	0		2021-03-Mar	x		15	173
11	06-09-21	A	y	6	34		2021-Q3	A	y	6	34		2021-Q2	A	y		4	47		4	47		2021-03-Mar	y		0	0
12	25-11-21	C	y	7	51		2021-Q4	C	y	7	51		2021-Q2	B	x		0	0		0	0		2021-04-Apr	x		0	0
13	03-03-21	B	x	5	83		2021-Q1	B	x	5	83		2021-Q2	B	y		3	14		3	14		2021-04-Apr	y		7	61
14	23-07-21	B	x	4	56		2021-Q3	B	x	4	56		2021-Q2	C	x		8	34		8	34		2021-05-May	x		8	34
15	18-09-21	A	y	5	73		2021-Q3	A	y	5	73		2021-Q2	C	y		7	37		7	37		2021-05-May	y		7	37
16	31-03-21	C	x	8	26		2021-Q1	C	x	8	26		2021-Q3	A	x		10	125		10	125		2021-07-Jul	x		18	164
17	27-01-21	A	x	2	43		2021-Q1	A	x	2	43		2021-Q3	A	y		20	125		20	125		2021-07-Jul	y		10	86
18	21-10-21	B	x	1	26		2021-Q4	B	x	1	26		2021-Q3	B	x		16	175		16	175		2021-08-Aug	x		1	60
19	20-07-21	B	x	5	43		2021-Q3	B	x	5	43		2021-Q3	B	y		10	86		10	86		2021-08-Aug	y		5	72
20	16-07-21	B	y	7	62		2021-Q3	B	y	7	62		2021-Q3	C	x		0	0		0	0		2021-09-Sep	x		7	76
21	25-12-21	C	y	3	87		2021-Q4	C	y	3	87		2021-Q3	C	y		5	72		5	72		2021-09-Sep	y		20	125
22	17-05-21	C	x	8	34		2021-Q2	C	x	8	34		2021-Q4	A	x		0	0		0	0		2021-10-Oct	x		1	26
23	05-07-21	A	x	9	65		2021-Q3	A	x	9	65		2021-Q4	A	y		0	0		0	0		2021-10-Oct	y		3	17
24	29-04-21	B	y	3	14		2021-Q2	B	y	3	14		2021-Q4	B	x		1	26		1	26		2021-11-Nov	x		0	0
25	24-01-21	A	x	5	98		2021-Q1	A	x	5	98		2021-Q4	B	y		3	17		3	17		2021-11-Nov	y		7	51
26	25-08-21	A	x	1	60		2021-Q3	A	x	1	60		2021-Q4	C	x		0	0		0	0		2021-12-Dec	x		0	0
27	07-05-21	C	y	7	37		2021-Q2	C	y	7	37		2021-Q4	C	y		12	191		12	191		2021-12-Dec	y		5	140
28	12-02-21	B	x	8	43		2021-Q1	B	x	8	43
29	17-04-21	A	y	4	47		2021-Q2	A	y	4	47					check:	=SUMIFS(J4:J33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
30	21-01-21	C	y	5	24		2021-Q1	C	y	5	24							=SUMIFS(K4:K33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
31	06-03-21	C	x	2	64		2021-Q1	C	x	2	64						15	173
32	27-09-21	B	x	7	76		2021-Q3	B	x	7	76						0	0
33	02-08-21	C	y	5	72		2021-Q3	C	y	5	72						13	126
34																	0	0		other functions on minisheet
35																	25	151		ASELECT
36	sample					=ACOMBINE(A37:E51,{1,2,3})					=ASUMIFS(ACOMBINE(A37:C51,),A37:C51,D37:E51)						5	24		ADATE
37	B	c	x	3	10		A	a	x			1	23				0	0		ACOMBINE
38	A	a	x	1	23		A	a	y			21	174				4	47
39	B	b	x	2	45		A	b	x			3	64				0	0
40	A	c	y	6	32		A	b	y			0	0				3	14
41	B	b	y	4	16		A	c	x			0	0				8	34
42	B	b	x	8	87		A	c	y			13	157				7	37
43	A	a	y	6	65		B	a	x			9	55				10	125
44	A	c	y	4	49		B	a	y			0	0				20	125
45	B	b	y	5	81		B	b	x			13	214				16	175
46	A	b	x	3	64		B	b	y			9	97				10	86
47	B	a	x	9	55		B	c	x			3	10				0	0
48	A	c	y	3	76		B	c	y			0	0				5	72
49	B	b	x	3	82												0	0
50	A	a	y	8	90												0	0
51	A	a	y	7	19												1	26
52																	3	17
53	Note: AGROUP easy to use formula will follow with the capability of																0	0
54	compacting the whole reports in an array																12	191
55
ASUMIFS post

Cell Formulas
Range	Formula
G3,R30,W5,M3,Q3,T3	G3	=FORMULATEXT(G4)
G4:K33	G4	=ADATE(A4:E33,"q",)
M4:O27	M4	=ACOMBINE(G4#,{1,2,3})
Q4:R27	Q4	=ASUMIFS(M4#,G4:I33,J4:K33)
T4:U27	T4	=ASUMIFS(ACOMBINE(ADATE(A4:E33,"q",),{1,2,3}),ASELECT(ADATE(A4:E33,"q",),,,,3,),J4:K33)
W4	W4	=FORMULATEXT(Z6)
W6:X27	W6	=ACOMBINE(ADATE(A4:E33,"m",),{1,3})
Z6:AA27	Z6	=ASUMIFS(ACOMBINE(ADATE(A4:E33,"m",),{1,3}),ASELECT(ADATE(A4:E33,"m",),,,,,{1,3}),J4:K33)
Q29	Q29	=FORMULATEXT(Q31)
Q31:Q54	Q31	=SUMIFS(J4:J33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
R31:R54	R31	=SUMIFS(K4:K33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
F36,K36	F36	=FORMULATEXT(G37)
G37:I48	G37	=ACOMBINE(A37:E51,{1,2,3})
L37:M48	L37	=ASUMIFS(ACOMBINE(A37:C51,),A37:C51,D37:E51)
Dynamic array formulas.

 

[Xlambda]

Noticed that when I posted first time the formula, I have copied the base case formula for recursive ASUMIFS that works for n<=2, (values array up to 2 columns), and not the general case one.
The only modification, n-1 to SEQUENCE(,n-1) ....APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),n-1)),x,))..to ....APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),SEQUENCE(,n-1))),x,)),,,,
Here is the general one and a complex example that uses array values of 4 columns, and other functions and reports, calls T_IFS , APPEND2H
!! recursive !! ASUMIFS(f,w,v)=LAMBDA(f,w,v,LET(r,ROWS(w),n,COLUMNS(v),x,MMULT(T_IFS(f,w,),INDEX(v,,n)),IF(n=1,APPEND2H(x,v,2),APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),SEQUENCE(,n-1))),x,))))
Other functions on minisheet: ARANDOM , ACOMBINE , ACOUNTIFS , ARUNTOT

=LAMBDA(f,w,v,
    LET(r,ROWS(w),n,COLUMNS(v),x,MMULT(T_IFS(f,w,),INDEX(v,,n)),
       IF(n=1,APPEND2H(x,v,2),APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),SEQUENCE(,n-1))),x,))
    )
)

LAMBDA 8.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1					Random generated array			Random generated values					Totals all values for all possible								Runing totals all values					Nr.operations		What shop/product/region
2													combinations of shop/products/region								by region				for each shop/product/region			registered no operation
3	shop	product	region		=ARANDOM(A4:C7,80)			=RANDARRAY(80,4,100,900,1)					=ACOMBINE(A4:C7,)			=ASUMIFS(M4#,E4#,H4#)					=ARUNTOT(P4#,-4)					=ACOUNTIFS(M4#,E4#)		=FILTER(M4#,NOT(Z4#))
4	A	X	East		D	Z	West	350	766	184	149		A	X	East	307	634	744	180		307	634	744	180		1		A	Y	East
5	B	Y	West		B	Z	West	478	722	634	242		A	X	North	598	519	116	177		598	519	116	177		1		A	Z	North
6	C	Z	North		B	X	South	397	545	449	141		A	X	South	858	655	801	720		858	655	801	720		1		A	Z	West
7	D		South		D	Y	West	448	357	170	569		A	X	West	445	115	553	372		445	115	553	372		1		C	Y	East
8					B	Z	North	450	698	720	520		A	Y	East	0	0	0	0		307	634	744	180		0		C	Z	East
9					C	Z	North	242	264	747	378		A	Y	North	408	550	663	1045		1006	1069	779	1222		2		C	Z	South
10					B	Y	South	273	176	480	485		A	Y	South	1681	1688	1859	1603		2539	2343	2660	2323		3		D	X	South
11					D	Y	North	352	383	802	150		A	Y	West	585	324	383	810		1030	439	936	1182		1		D	Z	South
12	other functions				B	Z	West	106	610	305	674		A	Z	East	189	336	820	186		496	970	1564	366		1
13	ARANDOM				D	Z	East	122	854	238	381		A	Z	North	0	0	0	0		1006	1069	779	1222		0
14	ACOMBINE				D	Z	West	837	408	236	511		A	Z	South	1798	2529	1350	3002		4337	4872	4010	5325		4
15	ACOUNTIFS				A	Z	South	596	498	437	832		A	Z	West	0	0	0	0		1030	439	936	1182		0
16	ARUNTOT				B	Y	West	392	665	733	819		B	X	East	705	1089	1261	771		1201	2059	2825	1137		2
17					B	Z	West	785	428	103	739		B	X	North	557	177	563	206		1563	1246	1342	1428		1
18					D	Z	North	630	303	238	783		B	X	South	653	1291	878	273		4990	6163	4888	5598		2
19					B	X	South	256	746	429	132		B	X	West	1212	1282	1365	546		2242	1721	2301	1728		2
20					B	Z	East	130	373	213	506		B	Y	East	1164	1115	747	1165		2365	3174	3572	2302		2
21					A	X	South	858	655	801	720		B	Y	North	209	584	403	625		1772	1830	1745	2053		1
22					C	X	East	877	302	206	721		B	Y	South	1507	717	1827	1533		6497	6880	6715	7131		3
23					B	Z	South	250	760	469	216		B	Y	West	392	665	733	819		2634	2386	3034	2547		1
24					A	Y	North	209	276	437	439		B	Z	East	1441	1547	1227	1014		3806	4721	4799	3316		3
25					C	Y	South	600	173	398	666		B	Z	North	1244	1477	1440	1384		3016	3307	3185	3437		3
26					B	Y	South	801	199	535	473		B	Z	South	2793	2599	2566	2355		9290	9479	9281	9486		5
27					A	Z	South	315	750	123	654		B	Z	West	2963	2915	3330	3605		5597	5301	6364	6152		6
28					A	Y	South	378	224	590	242		C	X	East	2022	1618	1251	1344		5828	6339	6050	4660		3
29					B	Y	East	810	744	240	291		C	X	North	755	1391	834	1153		3771	4698	4019	4590		2
30					B	Z	East	512	836	846	323		C	X	South	679	869	488	320		9969	10348	9769	9806		1
31					A	Y	South	830	875	515	660		C	X	West	130	433	806	135		5727	5734	7170	6287		1
32					D	Y	East	706	425	268	564		C	Y	East	0	0	0	0		5828	6339	6050	4660		0
33					A	X	West	445	115	553	372		C	Y	North	288	142	667	125		4059	4840	4686	4715		1
34					B	X	North	557	177	563	206		C	Y	South	600	173	398	666		10569	10521	10167	10472		1
35					C	X	East	476	884	767	483		C	Y	West	426	416	487	390		6153	6150	7657	6677		1
36					D	Y	North	480	852	496	172		C	Z	East	0	0	0	0		5828	6339	6050	4660		0
37					D	X	East	869	797	232	308		C	Z	North	565	667	1599	1067		4624	5507	6285	5782		2
38					A	Z	South	287	513	654	808		C	Z	South	0	0	0	0		10569	10521	10167	10472		0
39					B	Y	North	209	584	403	625		C	Z	West	561	863	754	660		6714	7013	8411	7337		1
40					B	X	East	129	553	852	633		D	X	East	1328	1000	751	884		7156	7339	6801	5544		2
41					C	Y	West	426	416	487	390		D	X	North	918	1136	564	1505		5542	6643	6849	7287		2
42					B	Z	South	829	216	184	564		D	X	South	0	0	0	0		10569	10521	10167	10472		0
43					B	Z	West	771	329	682	442		D	X	West	486	682	677	501		7200	7695	9088	7838		1
44					A	X	North	598	519	116	177		D	Y	East	2217	1755	821	1657		9373	9094	7622	7201		3
45					C	Y	North	288	142	667	125		D	Y	North	1282	1360	1808	823		6824	8003	8657	8110		3
46					C	X	South	679	869	488	320		D	Y	South	613	256	492	353		11182	10777	10659	10825		1
47					B	Y	East	354	371	507	874		D	Y	West	1139	812	389	898		8339	8507	9477	8736		2
48					C	X	North	231	855	695	543		D	Z	East	122	854	238	381		9495	9948	7860	7582		1
49					B	Z	South	891	510	885	228		D	Z	North	630	303	238	783		7454	8306	8895	8893		1
50					D	Y	South	613	256	492	353		D	Z	South	0	0	0	0		11182	10777	10659	10825		0
51					C	X	East	669	432	278	140		D	Z	West	2780	2894	1213	2715		11119	11401	10690	11451		5
52					D	Y	East	735	703	220	757
53					B	Z	West	432	222	713	683
54					D	Y	North	450	125	510	501
55					C	X	West	130	433	806	135
56					B	Y	South	433	342	812	575		left array goes down over this limit
57					A	Y	North	199	274	226	606		↓↓↓↓
ASUMIFS gen

Cell Formulas
Range	Formula
E3,H3,Z3,AB3,U3,M3,P3	E3	=FORMULATEXT(E4)
E4:G83	E4	=ARANDOM(A4:C7,80)
H4:K83	H4	=RANDARRAY(80,4,100,900,1)
M4:O51	M4	=ACOMBINE(A4:C7,)
P4:S51	P4	=ASUMIFS(M4#,E4#,H4#)
U4:X51	U4	=ARUNTOT(P4#,-4)
Z4:Z51	Z4	=ACOUNTIFS(M4#,E4#)
AB4:AD11	AB4	=FILTER(M4#,NOT(Z4#))
Dynamic array formulas.