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