ADVFLT

=ADVFLT(a,clm,crm,cla,cra)

a
array, regular, dynamic, table
clm
columns indexes for boolean multiplication, as constant integers horizontal array, 2, or {2,3}
crm
criteria text strings or numeric arguments for boolean multiplication, horizontal array of criteria corresponding to clm indexes {">3","quad"}
cla
columns indexes for boolean adding, as constant integers horizontal array, 2, or {2,3}
crm
criteria text strings or numeric arguments for boolean adding, horizontal array of criteria corresponding to cla indexes {">3","quad"}

Advanced Filter of any array, by any criteria, flexible boolean logic

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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
ABCDEFGHIJKLMNOPQRSTU
101-01-20Tynia Malone500QuadMidWest12560.88
201-01-20Kiki Sho38YanakiMidWest902.79=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},,)=ADVFLT(A1:F32,,,{4,4},{"quad","yanaki"})
301-01-20Hien Pham500YanakiMidWest8035.6303-01-20Hien Pham100Crested BeautMidWest1816.501-01-20Tynia Malone500QuadMidWest12560.88
401-01-20Chantel Mims500YanakiWest8035.6303-01-20Chantel Mims7YanakiMexico188.6501-01-20Kiki Sho38YanakiMidWest902.79
501-01-20Tynia Malone39QuadEast1481.4703-01-20Hien Pham5YanakiMexico139.7501-01-20Hien Pham500YanakiMidWest8035.63
601-01-20Kiki Sho48SunsetMidWest934.203-01-20Chantel Mims37QuadMexico1374.0501-01-20Chantel Mims500YanakiWest8035.63
701-01-20Hien Pham13QuadCanada580.9703-01-20Janis Figueroa525QuadMexico13188.9201-01-20Tynia Malone39QuadEast1481.47
802-01-20Tynia Malone27YanakiNorthWest641.4503-01-20Chantel Mims450SunsetNorthWest6714.5601-01-20Hien Pham13QuadCanada580.97
902-01-20Hien Pham13SunsetMidWest337.3504-01-20Hien Pham45SunsetCanada916.0902-01-20Tynia Malone27YanakiNorthWest641.45
1002-01-20Chantel Mims36SunsetMidWest732.8704-01-20Tynia Malone3SunsetWest74.8502-01-20Tynia Malone44QuadSouth1596.61
1102-01-20Tynia Malone44QuadSouth1596.6104-01-20Chantel Mims48Crested BeautWest934.203-01-20Chantel Mims7YanakiMexico188.65
1203-01-20Hien Pham100Crested BeautMidWest1816.504-01-20Chantel Mims100Crested BeautMidWest1746.503-01-20Hien Pham5YanakiMexico139.75
1303-01-20Chantel Mims7YanakiMexico188.6504-01-20Hien Pham2YanakiSouth55.903-01-20Chantel Mims37QuadMexico1374.05
1403-01-20Hien Pham5YanakiMexico139.7504-01-20Kiki Sho25QuadMexico907.1603-01-20Janis Figueroa525QuadMexico13188.92
1503-01-20Chantel Mims37QuadMexico1374.0504-01-20Chantel Mims8Crested BeautSouth215.604-01-20Hien Pham2YanakiSouth55.9
1603-01-20Janis Figueroa525QuadMexico13188.9204-01-20Kiki Sho34Crested BeautMidWest778.8604-01-20Kiki Sho25QuadMexico907.16
1703-01-20Chantel Mims450SunsetNorthWest6714.5604-01-20Chantel Mims275YanakiWest4419.5904-01-20Chantel Mims275YanakiWest4419.59
1804-01-20Hien Pham45SunsetCanada916.0905-01-20Chantel Mims200QuadEast5976.6
1904-01-20Tynia Malone3SunsetWest74.8505-01-20Hien Pham600YanakiEast9297.75
2004-01-20Chantel Mims48Crested BeautWest934.2dates between 3 and 4 Jan, product Quad or Yanaki05-01-20Chantel Mims28QuadMidWest1039.82
2104-01-20Chantel Mims100Crested BeautMidWest1746.5=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},{4,4},{"quad","yanaki"})05-01-20Kiki Sho525YanakiMidWest8437.41
2204-01-20Hien Pham2YanakiSouth55.903-01-20Chantel Mims7YanakiMexico188.65
2304-01-20Kiki Sho25QuadMexico907.1603-01-20Hien Pham5YanakiMexico139.75
2404-01-20Chantel Mims8Crested BeautSouth215.603-01-20Chantel Mims37QuadMexico1374.05=APIVOT(H22#,1,2,6,)
2504-01-20Kiki Sho34Crested BeautMidWest778.8603-01-20Janis Figueroa525QuadMexico13188.92(1\2) 6 vf=0Chantel MimsHien PhamJanis FigueroaKiki ShoGrand Total
2604-01-20Chantel Mims275YanakiWest4419.5904-01-20Hien Pham2YanakiSouth55.903-01-201562.7139.7513188.92014891.37
2705-01-20Chantel Mims200QuadEast5976.604-01-20Kiki Sho25QuadMexico907.1604-01-204419.5955.90907.165382.65
2805-01-20Hien Pham600YanakiEast9297.7504-01-20Chantel Mims275YanakiWest4419.59Grand Total5982.29195.6513188.92907.1620274.02
2905-01-20Janis Figueroa36Majestic BeautMidWest1069.47
3005-01-20Chantel Mims28QuadMidWest1039.82other function on minisheet APIVOT
3105-01-20Kiki Sho525YanakiMidWest8437.41
3205-01-20Chantel Mims6Majestic BeautCanada209.7
33
ADVFLT post
Cell Formulas
RangeFormula
H2,O24,H21,O2H2=FORMULATEXT(H3)
H3:M17H3=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},,)
O3:T21O3=ADVFLT(A1:F32,,,{4,4},{"quad","yanaki"})
H22:M28H22=ADVFLT(A1:F32,{1,1},{">2-jan-20","<5-jan-20"},{4,4},{"quad","yanaki"})
O25:T28O25=APIVOT(H22#,1,2,6,)
Dynamic array formulas.
 
Upvote 0

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
88
Office Version
  1. 365
Platform
  1. Windows
found a typo, should be SUM(clm)=0 instead of SUM(clm=0) (does not affect the outcome)
Excel Formula:
=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)
    )
)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top