| A |B|C |D| E | F | G | H |I| J | K | L | M |
--|-------|-|--|-|----------|------|------|------|-|--------|--------|---------|--------|--
1 | | | | | 2024 | 2024 | 2025 | 2025 | |RowCrit1|RowCrit2|ColCrit1 |ColCrit2|
2 | | | | | HY1 | HY2 | HY1 | HY2 | | 2025 | HY1 | Brand A | P1 |
3 | | | | | | | | | | | | Brand B | P3 |
4 | | | | |Prod.Line1| | | | | | | Brand D | |
5 |Brand A| |P1| | 500 | 70 | 60 | 80 | | | | | |
6 |Brand A| |P2| | 100 | 47 | 300 | 100 | | | | | |
7 |Brand A| |P2| | 800 | 21 | 200 | 360 | | Result | 60 | | |
8 |Brand B| |P1| | 90 | 56 | 150 | 578 | | | 150 | | |
9 |Brand C| |P4| | 45 | 700 | 790 | 800 | | | 980 | | |
10|Brand C| |P2| | 600 | 150 | 40 | 10 | | | 726 | | |
11| | | | |Subtotal | | | | | | 614 | | |
12| | | | | | | | | | | 85 | | |
13| | | | |Prod.Line2| | | | | | | | |
14|Brand D| |P1| | 900 | 90 | 980 | 453 | | | | | |
15|Brand D| |P1| | 125 | 854 | 726 | 850 | | | | | |
16|Brand D| |P3| | 70 | 860 | 614 | 140 | | | | | |
17|Brand D| |P4| | 842 | 250 | 85 | 215 | | | | | |
18|Brand E| |P4| | 300 | 324 | 450 | 430 | | | | | |
19| | | | |Subtotal | | | | | | | | |
I want to filter the list in based on multiple column and row criteria.
The row criteria are in Cell J2
and Cell K2
The column criteria are in Range L2:L4
and in Range M2:M4
The expected result you can see in Range K7:K12
.
With reference to the answer in this question I am able to include all the column criteria into the FILTER function:
=LET(a, COUNTIF(L2:L4,A5:A18),b, COUNTIF(M2:M4,C5:C18),FILTER(E5:E18,IFS(SUM(a)=0,b,SUM(b)=0,α,1,a*b),""))
However, I have no clue how to add the row criteria to the function.
Do you have any idea how to do it?
As said in the comments by rachel
the solution is:
=LET(a, COUNTIF(L2:L4,A3:A19),b,COUNTIF(M2:M4,C3:C19),FILTER(FILTER(E3:H19,(E1:H1=J2)*(E2:H2=K2),""),IFS(SUM(a)=0,b,SUM(b)=0,α,1,a*b),""))