Search code examples
excelexcel-formula

Using a list (range) of multiple column and row criteria in FILTER formula


  |   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?


Solution

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