Using a list (range) of column critiera (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 critiera.
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:

• 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,α*δ),""))