Search code examples
excelexcel-formula

Using FILTER formula with a list (range) of multiple column and row criteria (and ignore the criteria when criteria-list is empty)

  |   A    |B|C |D|  E  |F|  G   |   H  |   I  |   J  |K|  L   |   M  |N|   O   |   P   |   Q   |  
--|--------|-|--|-|-----|-|------|------|------|------|-|------|------|-|-------|-------|-------|-
1 |        | |  | |     | | 2024 | 2024 | 2025 | 2025 | |RowCr1|RowCr2| |ColCr1 |ColCr2 |ColCr3 |
2 |        | |  | |     | | HY1  |  HY2 |  HY1 |  HY2 | | 2025 |  HY1 | |Brand A|  P1   | Type1 |
3 |        | |  | |     | |      |      |      |      | |      |      | |Brand C|  P4   | Type2 |
4 |Brand A | |P1| |Type1| | 500  |  70  |  60  |  80  | |      |      | |Brand D|       |       |
5 |Brand A | |P2| |Type1| | 100  |  47  | 300  | 100  | |      |      | |       |       |       |      
6 |Brand A | |P2| |Type1| | 800  |  21  | 200  | 360  | |      |      | |Result1|Result2|       |
7 |Brand B | |P1| |Type2| |  90  |  56  | 150  | 578  | |      |      | |  60   |   60  |       |
8 |Brand C | |P4| |Type2| |  45  | 700  | 790  | 800  | |      |      | | 790   |  300  |       | 
9 |Brand C | |P2| |Type2| | 600  | 150  |  40  |  10  | |      |      | | 980   |  200  |       |
10|Brand D | |P1| |Type1| | 900  |  90  | 980  | 453  | |      |      | | 726   |  790  |       |
11|Brand D | |P1| |Type1| | 125  | 854  | 726  | 850  | |      |      | |  85   |   40  |       | 
12|Brand D | |P3| |Type3| |  70  | 860  | 614  | 140  | |      |      | |       |  980  |       | 
13|Brand D | |P4| |Type2| | 842  | 250  |  85  | 215  | |      |      | |       |  726  |       | 
14|Brand E | |P4| |Type2| | 300  | 324  | 450  | 430  | |      |      | |       |   85  |       |

I want to filter the data in Range A1:J18 based on the row criteria in Cell L2 and M2 and on the column criteria in Range O2:O4, Range P2:P4 and Range Q2:Q4.

With reference to the answer in this question I applied the following formula:

Cell O7 =LET(a,COUNTIF(O2:O4,A3:A14),
b,COUNTIF(P2:P4,C3:C14),c,COUNTIF(Q2:Q4,E3:E14),
FILTER(FILTER(G3:J14,(G1:J1=L2)*(G2:J2=M2),""),IFS(SUM(a)=0,b,SUM(b)=0,SUM(c)=0,1,a*b*c),""))

This formula works totally fine as long as the criteria in Range P2:P4 or Range Q2:Q4 is not empty.

When for example the criteria in Range P2:P4 is empty I would expect the results displayed in Range P7:P14.

However, the formula above returns no list at all. The result is just blank.

How do I need to modify the formula to make it work no matter which of the column criteria are empty?

Solution

  • When P1:P4 is not empty:

    enter image description here

    When P1:P4 is empty:

    enter image description here

    • Formula used in cell O7

    =LET(
     α, DROP(A1:J14,3),
     δ, COUNTIFS(O2:O4, TAKE(α,,1), O2:O4,"<>"),
     φ, COUNTIFS(P2:P4, INDEX(α,,3), P2:P4,"<>"),
     β, COUNTIFS(Q2:Q4, INDEX(α,,5), Q2:Q4,"<>"),
     ε, IFS(SUM(δ)=0,φ*β,SUM(φ)=0,δ*β,SUM(β)=0,δ*φ,SUM(δ*φ)=0,β,1,δ*φ*β),
     Σ, --CHOOSECOLS(FILTER(α, IF(SUM(ε)=0,SEQUENCE(ROWS(α))^0, ε),0),XMATCH(1, (L2=A1:J1)*(M2=A2:J2))),
     FILTER(Σ,1-ISERR(Σ)))

    Updated Formula Using Custom LAMBDA() :

    =LET(
     λ,LAMBDA(α,δ,ABS(AND(ISBLANK(δ))-MMULT(--(α=TOROW(δ)),{1;1;1}))),
     φ, FILTER(G4:J14,(G1:J1=L2)*(M2=G2:J2))*λ(A4:A14,O2:O4)*λ(C4:C14,P2:P4)*λ(E4:E14,Q2:Q4),
     FILTER(φ,φ>0))