| 0 | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Data List | Filter_01 | Filter_02 | ||||||
| 2 | date | brand | type | prod. | val. | 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. | val. | ||
| 8 | 2023-06-18 | Br_B | t4 | P1 | 80 | P2 | 80 | ||
| 9 | 2024-05-17 | Br_B | t4 | P4 | 25 | P3 | 122 | ||
| 10 | 2023-08-19 | Br_C | t3 | P5 | 85 | P4 | 25 | ||
| 11 | 2022-09-30 | Br_C | t5 | P3 | 70 | P5 | 85 | ||
| 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 this formula:
=LET(
a,COUNTIF(G2:G4,B2:B18)+AND(G2:G4=""),
b,COUNTIF(H2:H4,C2:C18)+AND(H2:H4=""),
c,FILTER(A2:E18,a*b,""),
d,CHOOSECOLS(c,4,5),
d)
This formula does not yet give the results displayed in Range G8:H11 because it does not list the prod. uniquely and sum up the val. per unique prod..
Although the solution from this question comes close to what I need it does not fully solve the issue in this case because I am looking for a formula in which I only have to define the filters once and not multiple times. Additionally, compared to the other question the filters are a bit more complex since they refer to a range with multiple criteria.
Summarized, I am looking for something like this:
=LET(
a,COUNTIF(G2:G4,B2:B18)+AND(G2:G4=""),
b,COUNTIF(H2:H4,C2:C18)+AND(H2:H4=""),
c,FILTER(A2:E18,a*b,""),
d,CHOOSECOLS(c,4,5),
e,unique_list_with_summed_up_values_based_on_a_and_b;
e)
How do I need to modify the formula to make it work?
Note: I am using Office365 (Version 2410) and I do not have the GROUPBY function yet.