[SOLVED] Create unique list and sum up values per unique row (without defining the filter criteria multiple times in the LET function)

Create unique list and sum up values per unique row (without defining the filter criteria multiple times in the LET function)

0	A	B	C	D	E	G	H
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	P1	80
11	2022-09-30	Br_C	t5	P3	70	P4	25
12	2024-04-25	Br_C	t5	P3	32	P5	85
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.

Solution

Give a try to the following formula-

=LET(x,FILTER(D3:E18,COUNTIFS(G2:G4,B3:B18)*COUNTIFS(H2:H5,C3:C18)),
y,UNIQUE(CHOOSECOLS(x,1)),
z,MAP(y,LAMBDA(t,SUM(CHOOSECOLS(FILTER(x,CHOOSECOLS(x,1)=t),2)))),
HSTACK(y,z))