Formula with words

[Tam21]

Hello,

I'm trying to find a formula that can calculate the costs for the different transports in the example underneath.
The costs can be found in the table. There are different costs per day (for example from day 0 till 10 €0,- ; day 11 till 15 €2,- etc. This depends on the customer, Transporter and transporter. The last column has been added since it calculates the costs from the row before if applicable (15 days - 11 days)*2,- = 8,00

Customer	Transporter	Truck	Days of transport	Costs
A	Jans	1'	17	?
A	Smith	1'	10	?
B	Smith	1'	40	?
B	A. Logistics	2' Long	20	?

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Table

Customer	Transporter	Container type	From days	Untill day	Costs per day
A	Jans	1'	0	10	0,00	0,00
A	Jans	1'	11	15	2,00	0,00
A	Jans	1'	16	1000	5,00	8,00
A	Jans	2'	0	10	0,00	0,00
A	Jans	2'	11	15	5,00	0,00
A	Jans	2'	16	1000	10,00	20,00
A	Smith	1'	0	5	0,00	0,00
A	Smith	1'	6	16	20,00	0,00
A	Smith	1'	17	10000	35,00	200,00
B	A. Logistics	1'	0	3	0,00	0,00
B	A. Logistics	1'	4	11	1,00	0,00
B	A. Logistics	1'	12	10000	5,00	7,00
B	A. Logistics	2' Long	0	3	0,00	0,00
B	A. Logistics	2' Long	4	11	14,00	0,00
B	A. Logistics	2' Long	12	10000	15,00	98,00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thank you in advance.

 

[Robert Mika]

Hello and welcome.
Why did you pot the 8 in second table?
What's the total cost per each line in top table.
Can you add the values?

 

[Marcelo Branco]

I suggest the use of a helper column (G) as below

A B C D E F G H I J K L M N O

Customer	Transporter	Truck	Days of transport	Costs		Helper (Row)		Customer	Transporter	Container type	From days	Untill day	Costs per day	Before
A	Jans	1'	17	18		4		A	Jans	1'	0	10	0	0
A	Smith	1'	10	100		9		A	Jans	1'	11	15	2	0
B	A. Logistics	1'	40	152		13		A	Jans	1'	16	1000	5	8
B	A. Logistics	2' Long	20	233		16		A	Jans	2'	0	10	0	0
								A	Jans	2'	11	15	5	0
								A	Jans	2'	16	1000	10	20
								A	Smith	1'	0	5	0	0
								A	Smith	1'	6	16	20	0
								A	Smith	1'	17	10000	35	200
								B	A. Logistics	1'	0	3	0	0
								B	A. Logistics	1'	4	11	1	0
								B	A. Logistics	1'	12	10000	5	7
								B	A. Logistics	2' Long	0	3	0	0
								B	A. Logistics	2' Long	4	11	14	0
								B	A. Logistics	2' Long	12	10000	15	98

<tbody>
</tbody>

Array formula in G2 (Helper column) copied down
=MATCH(D2,IF($I$1:$I$1000=A2,IF($J$1:$J$1000=B2,IF($K$1:$K$1000=C2,$L$1:$L$1000))))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

Formula in E2 copied down
=(D2-INDEX(L:L,G2)+1)*INDEX(N:N,G2)+INDEX(O:O,G2)

M.

 

[Marcelo Branco]

Complementing

But i think the column Before is not correct because 15 days of transport should cost 10, not 8.
(15-11+1)*2 = 5 * 2 = 10

M.

 

[Tam21]

Complementing

But i think the column Before is not correct because 15 days of transport should cost 10, not 8.
(15-11+1)*2 = 5 * 2 = 10

M.

That is correct, I'm sorry if I made it more complex because of this mistake. Thank you very much for the answer, it helped me a lot!

Kind regards

 

[Marcelo Branco]

That is correct, I'm sorry if I made it more complex because of this mistake. Thank you very much for the answer, it helped me a lot!

Kind regards

You are welcome and thanks for the feedback.

M.