| 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 | P2 |
4 | | | | |Prod.Line1| | | | | | | Brand C | |
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?