excelexcel-formula

Using a list (range) of column criteria (not single cells) in FILTER formula

A B C D E F
Revenue ColCrit1 ColCrit2
2 Brand A P1 500 Brand A P1
3 Brand A P2 100 Brand B P3
4 Brand A P2 800 Brand D
5 Brand B P1 90
6 Brand C P4 45
7 Brand C P2 600 Result 500
8 Brand D P1 900 90
9 Brand D P1 125 900
10 Brand D P3 70 125
11 Brand D P3 842 70
12 Brand E P4 300 842

I want to filter the list based on multiple column criteria.
The column criteria can be entered by the user in Range E2:E4 and Range F2:F4.

So far I have been only able to create a filter formula on single cell entries:

F7 = FILTER(C2:C12,IF(E2="",1,(A2:A12=E2))*IF(F2="",1,(B2:B12=F2)))

Do you know if there is a formula to apply the criteria as a range?
Something like this:

F7 = FILTER(C2:C12,IF(E2:E4="",1,(A2:A12=E2:E4))*IF(F2:F4="",1,(B2:B12=F2:F4)))
Solution

  • Here is one way of doing this:

    enter image description here

    • Formula used in cell E6

    =LET(
     α, COUNTIF(E2:E4,A2:A12),
     δ, COUNTIF(F2:F4,B2:B12),
     FILTER(C2:C12,IFS(SUM(α)=0,δ,SUM(δ)=0,α,1,α*δ),""))