Find a value matching a range upon several criteria

[ED38]

Hello,
I have hard time to to get the appropriate result with my formula so asking for your support :

In a list of data, each product has a cost associated upon a range of quantity at a certain date, I m looking for that associated cost as shown hereafter :

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Qty range					Unique list		Last		The sought formual in a spilled way!
2	product	Update date	TIER (Low)	TIER (High)	Cost value			product	Quantity	Update date	Nb of Tiers	Cost Value	Expected values
3	AAA	01/10/23	1	100	10			AAA	250	01/01/24	3		22
4	AAA	01/10/23	101	500	20			AAB	8	22/12/23	3		130
5	AAA	01/10/23	501	2000	40			AAC	3001	03/09/23	4		2750
6	AAA	01/01/24	1	100	12
7	AAA	01/01/24	101	500	22
8	AAA	01/01/24	501	2000	42
9	AAB	21/09/23	1	10	120
10	AAB	21/09/23	11	100	250
11	AAB	21/09/23	101	500	350
12	AAB	22/12/23	1	10	130
13	AAB	22/12/23	11	100	260
14	AAB	22/12/23	101	500	360
15	AAC	03/06/23	10	500	500
16	AAC	03/06/23	501	1000	750
17	AAC	03/06/23	1001	2500	1100
18	AAC	03/06/23	2501	10000	2500
19	AAC	03/09/23	10	400	550
20	AAC	03/09/23	401	1000	825
21	AAC	03/09/23	1001	3000	1210
22	AAC	03/09/23	3001	12000	2750
23	AAC	24/05/23	10	450	600
24	AAC	24/05/23	451	1200	900
25	AAC	24/05/23	1201	3500	1320
26	AAC	24/05/23	3501	15000	3000
27	…
Sheet2

Cell Formulas
Range	Formula
H3:H5	H3	=UNIQUE(A3:A26)
J3:J5	J3	=MAXIFS($B$3:$B$26,$A$3:$A$26,H3#)
K3:K5	K3	=COUNTIFS($A$3:$A$26,$H$3#,$B$3:$B$26,$J3#)
C4:C5,C24:C26,C20:C22,C16:C18,C13:C14,C10:C11,C7:C8	C4	=D3+1
E12:E14	E12	=E9+10
B9:B14	B9	=B3-10
B15:B16	B15	=B3-120
B17:B20	B17	=B4-120
B21:B24	B21	=B7-120
B25:B26	B25	=B10-120
E19:E22	E19	=E15*1.1
E23:E26	E23	=E15*1.2
Dynamic array formulas.

Thanks for your help

 

[hagia_sofia]

Hello, you could e.g. use:

=SUMIFS($E$3:$E$26,$A$3:$A$26,H3,$C$3:$C$26,"<="&I3,$D$3:$D$26,">="&I3,$B$3:$B$26,J3)

=FILTER($E$3:$E$26,($A$3:$A$26=H3)*($C$3:$C$26<=I3)*($D$3:$D$26>=I3)*($B$3:$B$26=J3))

 

[ED38]

hello hagia_sofia,

Thank you for the quick response.
the 1st formula using SUMIFS works well even in a "spilled way" :
=SUMIFS($E$3:$E$26;$A$3:$A$26;H3#;$C$3:$C$26;"<="&I3:I5;$D$3:$D$26;">="&I3:I5;$B$3:$B$26;J3#)

whereas it does not with the FILTER formula so I validate the 1st one!
thanks a lot.