complicated calculation of price formula

[Tripleseas]

Hello community

i'm working on worbook and i need to do some calculation like this :

depending on the price in the 3 differents rang ( Cell B3 to Cell D28 ) in comparisson the rang table , if that rang is detected i want the price in the base table to be multiplied by numbers of days in that month

I did some manual examples :
Cell D3 = 60 000 ---> it's in the rang 50 001 - 180 000 ----> result : 250 ( the price of the rang ) * 31 ( numbers of days of january )

i also seperated the rangs ! i want to the calculations of the months of rang 3 to be done based on column rang 3 and so on for the others.

hope i was clear and thank you for the help.

price formulas.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					RANG 3						Rang 1			RANG 2
2	Gare de péage	Rang 1	Rang 2	Rang 3	Janvier	Février	Mars	Avril	Mai	Juin	Juillet	Août	Septembre	Octobre	Novembre	Décembre
3	X1	120 000	72 000	60 000	7750
4	X2	80 000	48 000	40 000
5	X3	40 000	24 000	20 000
6	X4	100 000	60 000	50 000
7	X5	20 000	12 000	10 000
8	X6	100 000	60 000	50 000
9	X7	10 000	6 000	5 000	645,73
10	X8	70 000	42 000	35 000						Rang	Price
11	X9	20 000	12 000	10 000						0-5 000	20,83
12	X10	30 000	18 000	15 000						5 001 -50 000	83,33
13	X11	5 000	3 000	2 500						50 001 -180 000	250,00
14	X12	5 000	3 000	2 500
15	X13	5 000	3 000	2 500
16	X14	5 000	3 000	2 500
17	X15	180 000	108 000	90 000
18	X16	10 000	6 000	5 000
19	X17	5 000	3 000	2 500
20	X18	5 000	3 000	2 500
21	X19	15 000	9 000	7 500
22	X20	5 000	3 000	2 500
23	X21	5 000	3 000	2 500
24	X22	5 000	3 000	2 500
25	X23	5 000	3 000	2 500
26	X24	50 000	30 000	25 000
27	X25	45 000	27 000	22 500
28	X26	8 000	4 800	4 000
Feuil1

Cell Formulas
Range	Formula
E3	E3	=250*31
E9	E9	=20.83*31
K12	K12	=100/1.2

 

[Tripleseas]

Here is full sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		1	2	3	RANG 3						Rang 1			RANG 2
2	Gare de péage	Rang 1	Rang 2	Rang 3	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
3	X1	120000	72000	60000	7750	7000	7750	7500	7750	7500	7750	7750	7500	7750	7500	7750
4	X2	80000	48000	40000	2583	2333	2583	2500	2583	2500	7750	7750	7500	2583	2500	2583
5	X3	40000	24000	20000	2583	2333	2583	2500	2583	2500	2583	2583	2500	2583	2500	2583
6	X4	100000	60000	50000	2583	2333	2583	2500	2583	2500	7750	7750	7500	7750	7500	7750
7	X5	20000	12000	10000	2583	2333	2583	2500	2583	2500	2583	2583	2500	2583	2500	2583
8	X6	100000	60000	50000	2583	2333	2583	2500	2583	2500	7750	7750	7500	7750	7500	7750
9	X7	10000	6000	5000	646	583	646	625	646	625	2583	2583	2500	2583	2500	2583
10	X8	70000	42000	35000	2583	2333	2583	2500	2583	2500	7750	7750	7500	2583	2500	2583
11	X9	20000	12000	10000	2583	2333	2583	2500	2583	2500	2583	2583	2500	2583	2500	2583
12	X10	30000	18000	15000	2583	2333	2583	2500	2583	2500	2583	2583	2500	2583	2500	2583
13	X11	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
14	X12	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
15	X13	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
16	X14	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
17	X15	180000	108000	90000	7750	7000	7750	7500	7750	7500	7750	7750	7500	7750	7500	7750
18	X16	10000	6000	5000	646	583	646	625	646	625	2583	2583	2500	2583	2500	2583
19	X17	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
20	X18	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
21	X19	15000	9000	7500	2583	2333	2583	2500	2583	2500	2583	2583	2500	2583	2500	2583
22	X20	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
23	X21	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
24	X22	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
25	X23	5000	3000	2500	646	583	646	625	646	625	646	646	625	646	625	646
26	X24	50000	30000	25000	2583	2333	2583	2500	2583	2500	2583	2583	2500	2583	2500	2583
27	X25	45000	27000	22500	2583	2333	2583	2500	2583	2500	2583	2583	2500	2583	2500	2583
28	X26	8000	4800	4000	646	583	646	625	646	625	2583	2583	2500	646	625	646
Sheet1

Cell Formulas
Range	Formula
E3	E3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$D$3)*($S$4:$S$6>=$D3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($E$2,0))))
F3:F28	F3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$D3)*($S$4:$S$6>=$D3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($F$2,0))))
G3:G28	G3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$D3)*($S$4:$S$6>=$D3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($G$2,0))))
H3:H28	H3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$D3)*($S$4:$S$6>=$D3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($H$2,0))))
I3:I28	I3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$D3)*($S$4:$S$6>=$D3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($I$2,0))))
J3:J28	J3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$D3)*($S$4:$S$6>=$D3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($J$2,0))))
K3:K28	K3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$B3)*($S$4:$S$6>=$B3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($K$2,0))))
L3:L28	L3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$B3)*($S$4:$S$6>=$B3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($L$2,0))))
M3:M28	M3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$B3)*($S$4:$S$6>=$B3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($M$2,0))))
N3:N28	N3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$C3)*($S$4:$S$6>=$C3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($N$2,0))))
O3:O28	O3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$C3)*($S$4:$S$6>=$C3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($O$2,0))))
P3:P28	P3	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$C3)*($S$4:$S$6>=$C3),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH($P$2,0))))
E4:E28	E4	=SUM(INDEX($T$4:$T$6,SMALL(IF(($R$4:$R$6<=$D$3)*($S$4:$S$6>=$D4),MATCH(ROW($T$4:$T$6),ROW($T$4:$T$6)),""),ROWS($A$1:$A$1)))*(DAY(EOMONTH(E3,0))))

thank a lot sir. amazing solution