Search code examples
excelexcel-formula

How can I use filter function in Excel to filter based on selections including those that are "ALL"?

I would like to use filter function so that when various selections are made, it filters the data accordingly even if sometimes the selection is "All" or just left blank.

enter image description here

I can do this for one selection using:

=FILTER(A7:A10,A7:A10=b2,A7:A10)

but doesn't seem to work when I was to use multiple selections:

=FILTER(A7:A10,(A7:A10=b2,A7:A10)*(B7:B10=b3,B7:B10))

or

=FILTER(A7:A10,(A7:A10=b2)*(B7:B10=b3),A7:A10)
Solution

  • Show All Data If Filter Criteria Are "All" or Blank

    =LET(data,A7:B10,crit,B2:B3,
    OneOrFilter,LAMBDA(array,crit,IF(OR(crit="All",crit=""),1,array=crit)),
    nf,OneOrFilter(CHOOSECOLS(data,1),INDEX(crit,1)),
    lf,OneOrFilter(CHOOSECOLS(data,2),INDEX(crit,2)),
    IF(AND(nf=1,lf=1),data,FILTER(data,nf*lf,"")))

    i.e.

    =LET(
    OneOrFilter,LAMBDA(array,crit,IF(OR(crit="All",crit=""),1,array=crit)),
    nf,OneOrFilter(A7:A10,B2),
    lf,OneOrFilter(B7:B10,B3),
    IF(AND(nf=1,lf=1),A7:B10,FILTER(A7:B10,nf*lf,"")))
    • The OneOrFilter function returns either 1, if the criteria is "All" or blank, or an array of TRUE/FALSEs, as the result of comparing a column against a criterion.
    • In the final statement, check if the results for all columns (there could be more than 2) are 1 when the requirement is to show all data. Otherwise, show the filtered data.