AIF

[Xlambda]

AIF Array Include argument for Filter function, NEW!! REDUCE. Returns array of 0 and 1 useful in FILTER function as include argument. Calls AFLAT , CP
Also, as "IF" in the function name suggests, (Array IF), same function will be used in replacing COUNTIFS,SUMIFS,…all …IFS functions complex constructions. For now, will cover what the function can do as Include argument in Filter function.
The goal of the function is to be able to do compact complex boolean logic with arrays of criteria that we can write explicitly or even can live in spreadsheet ranges for dashboard constructions.
a: array
e: expression criteria argument array ( "<=3" ,3, or {">=3",4,"*ab"} )
[o]: operation argument, 0 or omitted, multiplication or boolean AND ; 1, adding or boolean OR
For every criteria found in expression criteria argument array "e", applies the comparison function CP and multiplies or adds the results , depending on "o" argument value.
If "e" array has 3 elements e1,e2,e3, and "o" is 0 or omitted :
AIF(a,e)=CP(a,e1)*CP(a,e2)*CP(a,e3)
If "o"=1 :
AIF(a,e,1)=CP(a,e1)+CP(a,e2)+CP(a,e3)

=LAMBDA(a,e,[o],LET(f,AFLAT(e),r,ROWS(f),IF(r=1,CP(a,e),LET(x,REDUCE(0,SEQUENCE(r),LAMBDA(v,i,v+CP(a,INDEX(f,i)))),IF(o,IF(x,1,0),IF(x=r,1,0))))))

CP(a,e) ComParison function. a,e arguments as in AIF

=LAMBDA(a,e,LET(x,LEFT(e,2),y,LEFT(e,1),z,SWITCH(x,"<=",x,">=",x,"<>",x,SWITCH(y,"<",y,">",y,"*",y,"!",y,"=")),w,SUBSTITUTE(e,z,"",1), v,IFERROR(--w,w),
    --SWITCH(z,"<>",a<>v,"<=",a<=v,">=",a>=v,"<",a<v,">",a>v,"*",ISNUMBER(SEARCH(v,a)),"!",ISNUMBER(FIND(v,a)),"=",a=v)
    )
)

 

[Xlambda]

AIF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Examples 2. AIF with dates
2									Saturdays and Sundays of November
3	Fri	Sat	Sun	Mon	Tue	Wed	Thu		=AIF(WEEKDAY(A4#,2),{6,7},1)*AIF(MONTH(A4#),11)
4	01-10-21	02-10-21	03-10-21	04-10-21	05-10-21	06-10-21	07-10-21		0	0	0	0	0	0	0
5	08-10-21	09-10-21	10-10-21	11-10-21	12-10-21	13-10-21	14-10-21		0	0	0	0	0	0	0
6	15-10-21	16-10-21	17-10-21	18-10-21	19-10-21	20-10-21	21-10-21		0	0	0	0	0	0	0
7	22-10-21	23-10-21	24-10-21	25-10-21	26-10-21	27-10-21	28-10-21		0	0	0	0	0	0	0
8	29-10-21	30-10-21	31-10-21	01-11-21	02-11-21	03-11-21	04-11-21		0	0	0	0	0	0	0
9	05-11-21	06-11-21	07-11-21	08-11-21	09-11-21	10-11-21	11-11-21		0	1	1	0	0	0	0
10	12-11-21	13-11-21	14-11-21	15-11-21	16-11-21	17-11-21	18-11-21		0	1	1	0	0	0	0
11	19-11-21	20-11-21	21-11-21	22-11-21	23-11-21	24-11-21	25-11-21		0	1	1	0	0	0	0
12	26-11-21	27-11-21	28-11-21	29-11-21	30-11-21	01-12-21	02-12-21		0	1	1	0	0	0	0
13	03-12-21	04-12-21	05-12-21	06-12-21	07-12-21	08-12-21	09-12-21		0	0	0	0	0	0	0
14	10-12-21	11-12-21	12-12-21	13-12-21	14-12-21	15-12-21	16-12-21		0	0	0	0	0	0	0
15	17-12-21	18-12-21	19-12-21	20-12-21	21-12-21	22-12-21	23-12-21		0	0	0	0	0	0	0
16	24-12-21	25-12-21	26-12-21	27-12-21	28-12-21	29-12-21	30-12-21		0	0	0	0	0	0	0
17	31-12-21	01-01-22	02-01-22	03-01-22	04-01-22	05-01-22	06-01-22		0	0	0	0	0	0	0
18	07-01-22	08-01-22	09-01-22	10-01-22	11-01-22	12-01-22	13-01-22		0	0	0	0	0	0	0
19	14-01-22	15-01-22	16-01-22	17-01-22	18-01-22	19-01-22	20-01-22		0	0	0	0	0	0	0
20	21-01-22	22-01-22	23-01-22	24-01-22	25-01-22	26-01-22	27-01-22		0	0	0	0	0	0	0
21	28-01-22	29-01-22	30-01-22	31-01-22	01-02-22	02-02-22	03-02-22		0	0	0	0	0	0	0
22	A4 forml:	=SEQUENCE(18,7,"1-oct-21")
23									Mon-Thu, for months Oct and Dec
24	Fri	Sat	Sun	Mon	Tue	Wed	Thu		=AIF(WEEKDAY(A25:G42,2),{">=1","<5"})*AIF(MONTH(A25:G42),{10,12},1)
25	01-10-21	02-10-21	03-10-21	04-10-21	05-10-21	06-10-21	07-10-21		0	0	0	1	1	1	1
26	08-10-21	09-10-21	10-10-21	11-10-21	12-10-21	13-10-21	14-10-21		0	0	0	1	1	1	1
27	15-10-21	16-10-21	17-10-21	18-10-21	19-10-21	20-10-21	21-10-21		0	0	0	1	1	1	1
28	22-10-21	23-10-21	24-10-21	25-10-21	26-10-21	27-10-21	28-10-21		0	0	0	1	1	1	1
29	29-10-21	30-10-21	31-10-21	01-11-21	02-11-21	03-11-21	04-11-21		0	0	0	0	0	0	0
30	05-11-21	06-11-21	07-11-21	08-11-21	09-11-21	10-11-21	11-11-21		0	0	0	0	0	0	0
31	12-11-21	13-11-21	14-11-21	15-11-21	16-11-21	17-11-21	18-11-21		0	0	0	0	0	0	0
32	19-11-21	20-11-21	21-11-21	22-11-21	23-11-21	24-11-21	25-11-21		0	0	0	0	0	0	0
33	26-11-21	27-11-21	28-11-21	29-11-21	30-11-21	01-12-21	02-12-21		0	0	0	0	0	1	1
34	03-12-21	04-12-21	05-12-21	06-12-21	07-12-21	08-12-21	09-12-21		0	0	0	1	1	1	1
35	10-12-21	11-12-21	12-12-21	13-12-21	14-12-21	15-12-21	16-12-21		0	0	0	1	1	1	1
36	17-12-21	18-12-21	19-12-21	20-12-21	21-12-21	22-12-21	23-12-21		0	0	0	1	1	1	1
37	24-12-21	25-12-21	26-12-21	27-12-21	28-12-21	29-12-21	30-12-21		0	0	0	1	1	1	1
38	31-12-21	01-01-22	02-01-22	03-01-22	04-01-22	05-01-22	06-01-22		0	0	0	0	0	0	0
39	07-01-22	08-01-22	09-01-22	10-01-22	11-01-22	12-01-22	13-01-22		0	0	0	0	0	0	0
40	14-01-22	15-01-22	16-01-22	17-01-22	18-01-22	19-01-22	20-01-22		0	0	0	0	0	0	0
41	21-01-22	22-01-22	23-01-22	24-01-22	25-01-22	26-01-22	27-01-22		0	0	0	0	0	0	0
42	28-01-22	29-01-22	30-01-22	31-01-22	01-02-22	02-02-22	03-02-22		0	0	0	0	0	0	0
43
44									first half of Oct and Dec
45	Fri	Sat	Sun	Mon	Tue	Wed	Thu		=AIF(DAY(A46:G63),{">=1","<16"})*AIF(MONTH(A46:G63),{10,12},1)
46	01-10-21	02-10-21	03-10-21	04-10-21	05-10-21	06-10-21	07-10-21		1	1	1	1	1	1	1
47	08-10-21	09-10-21	10-10-21	11-10-21	12-10-21	13-10-21	14-10-21		1	1	1	1	1	1	1
48	15-10-21	16-10-21	17-10-21	18-10-21	19-10-21	20-10-21	21-10-21		1	0	0	0	0	0	0
49	22-10-21	23-10-21	24-10-21	25-10-21	26-10-21	27-10-21	28-10-21		0	0	0	0	0	0	0
50	29-10-21	30-10-21	31-10-21	01-11-21	02-11-21	03-11-21	04-11-21		0	0	0	0	0	0	0
51	05-11-21	06-11-21	07-11-21	08-11-21	09-11-21	10-11-21	11-11-21		0	0	0	0	0	0	0
52	12-11-21	13-11-21	14-11-21	15-11-21	16-11-21	17-11-21	18-11-21		0	0	0	0	0	0	0
53	19-11-21	20-11-21	21-11-21	22-11-21	23-11-21	24-11-21	25-11-21		0	0	0	0	0	0	0
54	26-11-21	27-11-21	28-11-21	29-11-21	30-11-21	01-12-21	02-12-21		0	0	0	0	0	1	1
55	03-12-21	04-12-21	05-12-21	06-12-21	07-12-21	08-12-21	09-12-21		1	1	1	1	1	1	1
56	10-12-21	11-12-21	12-12-21	13-12-21	14-12-21	15-12-21	16-12-21		1	1	1	1	1	1	0
57	17-12-21	18-12-21	19-12-21	20-12-21	21-12-21	22-12-21	23-12-21		0	0	0	0	0	0	0
58	24-12-21	25-12-21	26-12-21	27-12-21	28-12-21	29-12-21	30-12-21		0	0	0	0	0	0	0
59	31-12-21	01-01-22	02-01-22	03-01-22	04-01-22	05-01-22	06-01-22		0	0	0	0	0	0	0
60	07-01-22	08-01-22	09-01-22	10-01-22	11-01-22	12-01-22	13-01-22		0	0	0	0	0	0	0
61	14-01-22	15-01-22	16-01-22	17-01-22	18-01-22	19-01-22	20-01-22		0	0	0	0	0	0	0
62	21-01-22	22-01-22	23-01-22	24-01-22	25-01-22	26-01-22	27-01-22		0	0	0	0	0	0	0
63	28-01-22	29-01-22	30-01-22	31-01-22	01-02-22	02-02-22	03-02-22		0	0	0	0	0	0	0
64
65									Saturdays and Sundays in 2022
66	Fri	Sat	Sun	Mon	Tue	Wed	Thu		=AIF(WEEKDAY(A67:G84,2),{6,7},1)*AIF(YEAR(A67:G84),2022)
67	01-10-21	02-10-21	03-10-21	04-10-21	05-10-21	06-10-21	07-10-21		0	0	0	0	0	0	0
68	08-10-21	09-10-21	10-10-21	11-10-21	12-10-21	13-10-21	14-10-21		0	0	0	0	0	0	0
69	15-10-21	16-10-21	17-10-21	18-10-21	19-10-21	20-10-21	21-10-21		0	0	0	0	0	0	0
70	22-10-21	23-10-21	24-10-21	25-10-21	26-10-21	27-10-21	28-10-21		0	0	0	0	0	0	0
71	29-10-21	30-10-21	31-10-21	01-11-21	02-11-21	03-11-21	04-11-21		0	0	0	0	0	0	0
72	05-11-21	06-11-21	07-11-21	08-11-21	09-11-21	10-11-21	11-11-21		0	0	0	0	0	0	0
73	12-11-21	13-11-21	14-11-21	15-11-21	16-11-21	17-11-21	18-11-21		0	0	0	0	0	0	0
74	19-11-21	20-11-21	21-11-21	22-11-21	23-11-21	24-11-21	25-11-21		0	0	0	0	0	0	0
75	26-11-21	27-11-21	28-11-21	29-11-21	30-11-21	01-12-21	02-12-21		0	0	0	0	0	0	0
76	03-12-21	04-12-21	05-12-21	06-12-21	07-12-21	08-12-21	09-12-21		0	0	0	0	0	0	0
77	10-12-21	11-12-21	12-12-21	13-12-21	14-12-21	15-12-21	16-12-21		0	0	0	0	0	0	0
78	17-12-21	18-12-21	19-12-21	20-12-21	21-12-21	22-12-21	23-12-21		0	0	0	0	0	0	0
79	24-12-21	25-12-21	26-12-21	27-12-21	28-12-21	29-12-21	30-12-21		0	0	0	0	0	0	0
80	31-12-21	01-01-22	02-01-22	03-01-22	04-01-22	05-01-22	06-01-22		0	1	1	0	0	0	0
81	07-01-22	08-01-22	09-01-22	10-01-22	11-01-22	12-01-22	13-01-22		0	1	1	0	0	0	0
82	14-01-22	15-01-22	16-01-22	17-01-22	18-01-22	19-01-22	20-01-22		0	1	1	0	0	0	0
83	21-01-22	22-01-22	23-01-22	24-01-22	25-01-22	26-01-22	27-01-22		0	1	1	0	0	0	0
84	28-01-22	29-01-22	30-01-22	31-01-22	01-02-22	02-02-22	03-02-22		0	1	1	0	0	0	0
85
Examples 3

Cell Formulas
Range	Formula
I3,I66,I45,I24	I3	=FORMULATEXT(I4)
A4:G21	A4	=SEQUENCE(18,7,"1-oct-21")
I4:O21	I4	=AIF(WEEKDAY(A4#,2),{6,7},1)*AIF(MONTH(A4#),11)
B22	B22	=FORMULATEXT(A4)
I25:O42	I25	=AIF(WEEKDAY(A25:G42,2),{">=1","<5"})*AIF(MONTH(A25:G42),{10,12},1)
I46:O63	I46	=AIF(DAY(A46:G63),{">=1","<16"})*AIF(MONTH(A46:G63),{10,12},1)
I67:O84	I67	=AIF(WEEKDAY(A67:G84,2),{6,7},1)*AIF(YEAR(A67:G84),2022)
Dynamic array formulas.