# Formula with words

#### Tam21

##### New Member
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>

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Robert Mika

##### MrExcel MVP
Hello and welcome.
Why did you pot the 8 in second table?
What's the total cost per each line in top table.

#### Marcelo Branco

##### MrExcel MVP
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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.

Replies
1
Views
137
Replies
1
Views
230
Replies
7
Views
144
Replies
0
Views
177
Replies
1
Views
139

1,195,934
Messages
6,012,384
Members
441,693
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back