| 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.
2
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))))))
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))))))
2
=LET(c,CHOOSECOLS,
a,FILTER(A:E,ISNUMBER(A:A)),
x,LAMBDA(y,z,(1-ISNA(XMATCH(c(a,y),z))+AND(z=""))),
UNIQUE(FILTER(c(a,4),(c(a,1)<=G2)*x(2,H2:H3)*x(3,I2:I4))))
This formula uses ETA. Where I declared c to be CHOOSECOLS,
a to be the filter of A:E of rows containing dates (numbers) in column A,
x being (1-ISNA(XMATCH(CHOOSECOLS(a,y),z))+AND(z="")) where y is a numerical input to get the yth column of a and z being the filter value range.
Then x(2,H2:H3) means (1-ISNA(XMATCH(CHOOSECOLS(FILTER(A:E,IS NUMBER(A:A)),2),H2:H3))+AND(H2:JE=""))