ADVFLT

[Xlambda]

ADVFLT Advanced Filter of any array, by any criteria, boolean logic, similar to slicers functionality , no headers needed. calls T_AFA , T_AFM
-a, any array, regular, dynamic or table
-clm, columns indexes used for boolean multiplication, if 0 or omitted, no boolean multiplication
-crm, criteria used for boolean multiplication, if 0 or omitted, no boolean multiplication criteria
-cla, columns indexes for boolean adding, if 0 or omitted, no boolean adding
-cra, criteria used for boolean adding, if 0 or omitted, no boolean adding criteria
Other function on minisheet APIVOT. Complements also ASELECT for any scenario of array extraction or filtering.

=LAMBDA(a,clm,crm,cla,cra,
    LET(tm,IF(SUM(clm=0),1,T_AFM(a,clm,crm,1)),
       ta,IF(SUM(cla)=0,1,T_AFA(a,cla,cra,0)),
       FILTER(a,tm*ta)
    )
)

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
1	01-01-20	Tynia Malone	500	Quad	MidWest	12560.88
2	01-01-20	Kiki Sho	38	Yanaki	MidWest	902.79		=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},,)							=ADVFLT(A1:F32,,,{4,4},{"quad","yanaki"})
3	01-01-20	Hien Pham	500	Yanaki	MidWest	8035.63		03-01-20	Hien Pham	100	Crested Beaut	MidWest	1816.5		01-01-20	Tynia Malone	500	Quad	MidWest	12560.88
4	01-01-20	Chantel Mims	500	Yanaki	West	8035.63		03-01-20	Chantel Mims	7	Yanaki	Mexico	188.65		01-01-20	Kiki Sho	38	Yanaki	MidWest	902.79
5	01-01-20	Tynia Malone	39	Quad	East	1481.47		03-01-20	Hien Pham	5	Yanaki	Mexico	139.75		01-01-20	Hien Pham	500	Yanaki	MidWest	8035.63
6	01-01-20	Kiki Sho	48	Sunset	MidWest	934.2		03-01-20	Chantel Mims	37	Quad	Mexico	1374.05		01-01-20	Chantel Mims	500	Yanaki	West	8035.63
7	01-01-20	Hien Pham	13	Quad	Canada	580.97		03-01-20	Janis Figueroa	525	Quad	Mexico	13188.92		01-01-20	Tynia Malone	39	Quad	East	1481.47
8	02-01-20	Tynia Malone	27	Yanaki	NorthWest	641.45		03-01-20	Chantel Mims	450	Sunset	NorthWest	6714.56		01-01-20	Hien Pham	13	Quad	Canada	580.97
9	02-01-20	Hien Pham	13	Sunset	MidWest	337.35		04-01-20	Hien Pham	45	Sunset	Canada	916.09		02-01-20	Tynia Malone	27	Yanaki	NorthWest	641.45
10	02-01-20	Chantel Mims	36	Sunset	MidWest	732.87		04-01-20	Tynia Malone	3	Sunset	West	74.85		02-01-20	Tynia Malone	44	Quad	South	1596.61
11	02-01-20	Tynia Malone	44	Quad	South	1596.61		04-01-20	Chantel Mims	48	Crested Beaut	West	934.2		03-01-20	Chantel Mims	7	Yanaki	Mexico	188.65
12	03-01-20	Hien Pham	100	Crested Beaut	MidWest	1816.5		04-01-20	Chantel Mims	100	Crested Beaut	MidWest	1746.5		03-01-20	Hien Pham	5	Yanaki	Mexico	139.75
13	03-01-20	Chantel Mims	7	Yanaki	Mexico	188.65		04-01-20	Hien Pham	2	Yanaki	South	55.9		03-01-20	Chantel Mims	37	Quad	Mexico	1374.05
14	03-01-20	Hien Pham	5	Yanaki	Mexico	139.75		04-01-20	Kiki Sho	25	Quad	Mexico	907.16		03-01-20	Janis Figueroa	525	Quad	Mexico	13188.92
15	03-01-20	Chantel Mims	37	Quad	Mexico	1374.05		04-01-20	Chantel Mims	8	Crested Beaut	South	215.6		04-01-20	Hien Pham	2	Yanaki	South	55.9
16	03-01-20	Janis Figueroa	525	Quad	Mexico	13188.92		04-01-20	Kiki Sho	34	Crested Beaut	MidWest	778.86		04-01-20	Kiki Sho	25	Quad	Mexico	907.16
17	03-01-20	Chantel Mims	450	Sunset	NorthWest	6714.56		04-01-20	Chantel Mims	275	Yanaki	West	4419.59		04-01-20	Chantel Mims	275	Yanaki	West	4419.59
18	04-01-20	Hien Pham	45	Sunset	Canada	916.09									05-01-20	Chantel Mims	200	Quad	East	5976.6
19	04-01-20	Tynia Malone	3	Sunset	West	74.85									05-01-20	Hien Pham	600	Yanaki	East	9297.75
20	04-01-20	Chantel Mims	48	Crested Beaut	West	934.2		dates between 3 and 4 Jan, product Quad or Yanaki							05-01-20	Chantel Mims	28	Quad	MidWest	1039.82
21	04-01-20	Chantel Mims	100	Crested Beaut	MidWest	1746.5		=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},{4,4},{"quad","yanaki"})							05-01-20	Kiki Sho	525	Yanaki	MidWest	8437.41
22	04-01-20	Hien Pham	2	Yanaki	South	55.9		03-01-20	Chantel Mims	7	Yanaki	Mexico	188.65
23	04-01-20	Kiki Sho	25	Quad	Mexico	907.16		03-01-20	Hien Pham	5	Yanaki	Mexico	139.75
24	04-01-20	Chantel Mims	8	Crested Beaut	South	215.6		03-01-20	Chantel Mims	37	Quad	Mexico	1374.05		=APIVOT(H22#,1,2,6,)
25	04-01-20	Kiki Sho	34	Crested Beaut	MidWest	778.86		03-01-20	Janis Figueroa	525	Quad	Mexico	13188.92		(1\2) 6 vf=0	Chantel Mims	Hien Pham	Janis Figueroa	Kiki Sho	Grand Total
26	04-01-20	Chantel Mims	275	Yanaki	West	4419.59		04-01-20	Hien Pham	2	Yanaki	South	55.9		03-01-20	1562.7	139.75	13188.92	0	14891.37
27	05-01-20	Chantel Mims	200	Quad	East	5976.6		04-01-20	Kiki Sho	25	Quad	Mexico	907.16		04-01-20	4419.59	55.9	0	907.16	5382.65
28	05-01-20	Hien Pham	600	Yanaki	East	9297.75		04-01-20	Chantel Mims	275	Yanaki	West	4419.59		Grand Total	5982.29	195.65	13188.92	907.16	20274.02
29	05-01-20	Janis Figueroa	36	Majestic Beaut	MidWest	1069.47
30	05-01-20	Chantel Mims	28	Quad	MidWest	1039.82									other function on minisheet			APIVOT
31	05-01-20	Kiki Sho	525	Yanaki	MidWest	8437.41
32	05-01-20	Chantel Mims	6	Majestic Beaut	Canada	209.7
33
ADVFLT post

Cell Formulas
Range	Formula
H2,O24,H21,O2	H2	=FORMULATEXT(H3)
H3:M17	H3	=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},,)
O3:T21	O3	=ADVFLT(A1:F32,,,{4,4},{"quad","yanaki"})
H22:M28	H22	=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},{4,4},{"quad","yanaki"})
O25:T28	O25	=APIVOT(H22#,1,2,6,)
Dynamic array formulas.

 

[Xlambda]

found a typo, should be SUM(clm)=0 instead of SUM(clm=0) (does not affect the outcome)

=LAMBDA(a,clm,crm,cla,cra,
    LET(tm,IF(SUM(clm)=0,1,T_AFM(a,clm,crm,1)),
        ta,IF(SUM(cla)=0,1,T_AFA(a,cla,cra,0)),
        FILTER(a,tm*ta)
    )
)