excelexcel-formula

Create a list based on multiple filter criteria without row limitation in COUNTIF formula

0 A B C D E F G H I
1 Data List Filter_01 Filter_02 Filter_03
2 date brand type prod. val. 2023-12-31 Br_B t1
3 2022-05-10 Br_A t1 P1 15 Br_C t2
4 2023-01-15 Br_A t1 P1 18 t3
5 2022-08-12 Br_B t2 P2 30
6 2022-09-15 Br_B t3 P3 50 Result
7 2023-06-15 Br_B t1 P3 20 prod.
8 2023-06-18 Br_B t4 P1 80 P2
9 2024-05-17 Br_B t4 P4 25 P3
10 2023-08-19 Br_C t3 P5 85 P5
11 2022-09-30 Br_C t5 P3 70
12 2024-04-25 Br_C t5 P3 32
13 2025-08-29 Br_D t3 P1 45
14 2022-03-25 Br_E t1 P2 60
15 2022-03-26 Br_E t3 P2 85
16 2024-05-20 Br_E t5 P3 90
17 2024-06-03 Br_E t2 P4 15
18 2024-07-30 Br_E t1 P4 26

In Cell G8 I have implemented the below formula. It filters the list in Column A:E based on the criteria in Cell G2, in Range H2:H4 and Range I2:I4:

=LET(
a,COUNTIF(H2:H4,B1:B20)+AND(H2:H4=""),
b,COUNTIF(I2:I4,C1:C20)+AND(I2:I4=""),
c,FILTER(A1:E20,a*b,""),
d,A2:E2,
e,VSTACK(d,c),
f,MATCH(A2,CHOOSEROWS(e,1),0),
g,FILTER(e,CHOOSECOLS(e,1)<=G2),
h,DROP(UNIQUE(g),1),
i,VSTACK(d,h),
j,XLOOKUP(D2,CHOOSEROWS(i,1),i,NA(),0),
k,DROP(UNIQUE(j),1),
k)

All this works fine.

Now, I am wondering if it is somehow possible to get rid of the row limitations in the COUNTIF part of the formula so it looks like this:

=LET(
a,COUNTIF(H2:H4,B:B)+AND(H2:H4=""),
b,COUNTIF(I2:I4,C:C)+AND(I2:I4=""),
c,FILTER(A:E,a*b,""),
d,A2:E2,
e,VSTACK(d,c),
f,MATCH(A2,CHOOSEROWS(e,1),0),
g,FILTER(e,CHOOSECOLS(e,1)<=G2),
h,DROP(UNIQUE(g),1),
i,VSTACK(d,h),
j,XLOOKUP(D2,CHOOSEROWS(i,1),i,NA(),0),
k,DROP(UNIQUE(j),1),
k)

However, this formula returns #VALUE!.

How do I need to modify the formula to make it work?

Note: I like that in the formula it is very easy to add additional filters by adding COUNTIF variables. Therefore, the solution should also keep this flexibility.

Solution

  • I like using ISNUMBER(MATCH(,,0)) as it takes arrays unlike COUNTIF.

    Using full columns we can use the A.:.D referencing.

    =LET(
    rng,A.:.D,
    f_1,G2,
    f_2,H2:H4,
    f_3,I2:I4,
    _a,CHOOSECOLS(rng,1),
    _b,CHOOSECOLS(rng,2),
    _c,CHOOSECOLS(rng,3),
    _d,CHOOSECOLS(rng,4),
    UNIQUE(FILTER(
        _d,
        (ISNUMBER(MATCH(_a,FILTER(_a,_a<=f_1),0)))*
        (ISNUMBER(MATCH(_b,f_2,0)))*
        (ISNUMBER(MATCH(_c,f_3,0))))))

    enter image description here

    If one does not have the A.:.D references yet we can just do another filter:

    =LET(
    rng,A:D,
    f_1,G2,
    f_2,H2:H4,
    f_3,I2:I4,
    flt,FILTER(rng,CHOOSECOLS(rng,1)<>""),
    _a,CHOOSECOLS(flt,1),
    _b,CHOOSECOLS(flt,2),
    _c,CHOOSECOLS(flt,3),
    _d,CHOOSECOLS(flt,4),
    UNIQUE(FILTER(
        _d,
        (ISNUMBER(MATCH(_a,FILTER(_a,_a<=f_1),0)))*
        (ISNUMBER(MATCH(_b,f_2,0)))*
        (ISNUMBER(MATCH(_c,f_3,0))))))