T_IFS

[Xlambda]

T_IFS !! recursive !! tool-recursive lambda that creates pattern array used in MMULT formula construction as only alternative to SUMIFS COUNTIFS…formulas that are limited to range arguments.
One of Geert's comments on YT , inspired me in trying to address the drawbacks of SUMIFS, COUNTIFS...*IFS functions that can handle only range arguments and not array arguments. Thank him for that!!!
This tool is the piston of the motor who is able to overcame this drawbacks, and even more, can leed us to be able to write lambdas that can do grouping or "group by" as in PT or PQ.
What the formula does, is very simple, is comparing ("=" operator) vertical arrays with horizontal arrays and multiplies the result. (v1=h1)*(v2=h2)*.....*(vn=hn), recursively one (vk=hk) at a time.
The outcome will be an array of rows(v) x columns (h) full of 0 and 1 , useful in MMULT constructions for counting or summing.

=LAMBDA(f,w,p,
    LET(k,IF(COUNTA(p)=1,1,p),cf,COLUMNS(f),sf,SEQUENCE(ROWS(f)),cw,COLUMNS(w),sw,SEQUENCE(ROWS(w)),
       n,IF(cf=cw,cf,"x"),v,INDEX(f,,n),h,INDEX(w,,n),
       IF(n="x","check arrays",IF(n=1,k*(v=TRANSPOSE(h)),k*T_IFS(INDEX(f,sf,SEQUENCE(,n-1)),INDEX(w,sw,SEQUENCE(,n-1)),v=TRANSPOSE(h))))
    )
)

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	AD
1	tool-recursive lambda that creates pattern array used in MMULT formula construction as only alternative to SUMIFS COUNTIFS…formulas that are limited to range arguments
2	find and within array should have same width(same clm nr)																									MMULT construnction,COUNTIFS alternative
3						find or criteria array																				=MMULT(J5#,SEQUENCE(ROWS(A5:D19))^0)
4	"within" array					=ACOMBINE(A5:C19,)				=T_IFS(F5#,A5:C19,)																	check:	=COUNTIFS(A5:A19,F5:F16,B5:B19,G5:G16,C5:C19,H5:H16)
5	B	c	x	3		A	a	x		0	1	0	0	0	0	0	0	0	0	0	0	0	0	0		1		1
6	A	a	x	1		A	a	y		0	0	0	0	0	0	1	0	0	0	0	0	0	1	1		3		3
7	B	b	x	2		A	b	x		0	0	0	0	0	0	0	0	0	1	0	0	0	0	0		1		1
8	A	c	y	6		A	b	y		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
9	B	b	y	4		A	c	x		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
10	B	b	x	8		A	c	y		0	0	0	1	0	0	0	1	0	0	0	1	0	0	0		3		3
11	A	a	y	6		B	a	x		0	0	0	0	0	0	0	0	0	0	1	0	0	0	0		1		1
12	A	c	y	4		B	a	y		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
13	B	b	y	5		B	b	x		0	0	1	0	0	1	0	0	0	0	0	0	1	0	0		3		3
14	A	b	x	3		B	b	y		0	0	0	0	1	0	0	0	1	0	0	0	0	0	0		2		2
15	B	a	x	9		B	c	x		1	0	0	0	0	0	0	0	0	0	0	0	0	0	0		1		1
16	A	c	y	3		B	c	y		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
17	B	b	x	3
18	A	a	y	8																						MMULT construnction,SUMIFS alternative
19	A	a	y	7						check- this is the sequence of calculations done by the formula																=MMULT(J5#,D5:D19)
20							=(G5:G16=TRANSPOSE(B5:B19))*(H5:H16=TRANSPOSE(C5:C19))*(F5:F16=TRANSPOSE(A5:A19))																				check:	=SUMIFS(D5:D19,A5:A19,F5:F16,B5:B19,G5:G16,C5:C19,H5:H16)
21										0	1	0	0	0	0	0	0	0	0	0	0	0	0	0		1		1
22										0	0	0	0	0	0	1	0	0	0	0	0	0	1	1		21		21
23		other functions on minisheet								0	0	0	0	0	0	0	0	0	1	0	0	0	0	0		3		3
24		ACOMBINE								0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
25										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
26										0	0	0	1	0	0	0	1	0	0	0	1	0	0	0		13		13
27										0	0	0	0	0	0	0	0	0	0	1	0	0	0	0		9		9
28										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
29										0	0	1	0	0	1	0	0	0	0	0	0	1	0	0		13		13
30										0	0	0	0	1	0	0	0	1	0	0	0	0	0	0		9		9
31										1	0	0	0	0	0	0	0	0	0	0	0	0	0	0		3		3
32										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0		0
33
34										=T_IFS(F35:H36,A5:C19,)
35						A	c	y		0	0	0	1	0	0	0	1	0	0	0	1	0	0	0
36						B	a	x		0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
37
T_IFS post

Cell Formulas
Range	Formula
Z3,Z19	Z3	=FORMULATEXT(Z5)
F4,J4,J34,AB20,AB4	F4	=FORMULATEXT(F5)
F5:H16	F5	=ACOMBINE(A5:C19,)
J5:X16	J5	=T_IFS(F5#,A5:C19,)
Z5:Z16	Z5	=MMULT(J5#,SEQUENCE(ROWS(A5:D19))^0)
AB5:AB16	AB5	=COUNTIFS(A5:A19,F5:F16,B5:B19,G5:G16,C5:C19,H5:H16)
G20	G20	=FORMULATEXT(J21)
J21:X32	J21	=(G5:G16=TRANSPOSE(B5:B19))*(H5:H16=TRANSPOSE(C5:C19))*(F5:F16=TRANSPOSE(A5:A19))
Z21:Z32	Z21	=MMULT(J5#,D5:D19)
AB21:AB32	AB21	=SUMIFS(D5:D19,A5:A19,F5:F16,B5:B19,G5:G16,C5:C19,H5:H16)
J35:X36	J35	=T_IFS(F35:H36,A5:C19,)
Dynamic array formulas.