# Formula for adding taxes & tpt vaue

#### RAMU

##### Active Member
Dear All,

I have two workbooks, one is containing taxes & TPT (transportation) cost of 60 items like mentioned below:
workbook1

 B C D E F 1 ITEM ED CST VAT TPT 2 A 2% 20 3 B 13.50% 3 4 C 5 D 2% 20 6 E 4 5 7 F 13.50% 3

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

And the other workbook is containing a value like the following:
workbook2

 A B C D 1 ITEM QTY VALUE TOTAL VALUE 2 A 50 2000 3 B 100 5000 4 B 100 4050 5 C 40 600 6 C 40 540 7 E 20 300 8 B 100 5000 9 A 50 2500

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 89px"></colgroup><tbody>
</tbody>
Now I want a formula in column D of workbook2 that will add the taxes automatically with value ( sequence would be same as mentioned in the column of workbook1), then TPT cost will be added on after calculating its total cost. In workbook1 TPT is mentioned per unit basis. Ultimately TOTAL VALUE will show the result.

RAMU

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

##### Well-known Member
Dear All,

I have two workbooks, one is containing taxes & TPT (transportation) cost of 60 items like mentioned below:
workbook1

 B C D E F 1 ITEM ED CST VAT TPT 2 A 2% 20 3 B 13.50% 3 4 C 5 D 2% 20 6 E 4 5 7 F 13.50% 3

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

And the other workbook is containing a value like the following:
workbook2

 A B C D 1 ITEM QTY VALUE TOTAL VALUE 2 A 50 2000 3 B 100 5000 4 B 100 4050 5 C 40 600 6 C 40 540 7 E 20 300 8 B 100 5000 9 A 50 2500

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 89px"></colgroup><tbody>
</tbody>
Now I want a formula in column D of workbook2 that will add the taxes automatically with value ( sequence would be same as mentioned in the column of workbook1), then TPT cost will be added on after calculating its total cost. In workbook1 TPT is mentioned per unit basis. Ultimately TOTAL VALUE will show the result.

RAMU

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ITEM </td><td style=";">ED </td><td style=";">CST </td><td style=";">VAT </td><td style=";">TPT</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2%</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B </td><td style="text-align: right;;">13.50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">C </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">D </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2%</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">E </td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">F </td><td style="text-align: right;;">13.50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Taxes & TPT</p><br /><br />

You can use this formula:

#### RAMU

##### Active Member
Lil misunderstanding. In E2 value will be 3040. See its value is 2000. therefore taxes will be added 2% only. 2% of 2000 is 40. And TPT is 20 per unit. so tpt cost will be 50*20 = 1000. so total taxes & TPT amount is 1040. Now 1040 will be added with 2000. So Total value will be 3040.

Regards
RAMU

##### Well-known Member
Lil misunderstanding. In E2 value will be 3040. See its value is 2000. therefore taxes will be added 2% only. 2% of 2000 is 40. And TPT is 20 per unit. so tpt cost will be 50*20 = 1000. so total taxes & TPT amount is 1040. Now 1040 will be added with 2000. So Total value will be 3040.

Regards
RAMU
Try this:

=D2*(1+SUM(OFFSET(INDEX('Taxes & TPT'!C:C,MATCH(B2,'Taxes & TPT'!B:B,0)),0,0,1,3)))+C2*INDEX('Taxes & TPT'!F:F,MATCH(B2,'Taxes & TPT'!B:B,0))

#### RAMU

##### Active Member

Dear Friend,

I was wrong. Your formula is perfect based on my statement. Actually If a product has multiple taxes like ED & CST then First it will add the value & ED value first, then CST or VAT will be calculated on that value.

Suppose product G has 13.5% ED, 2% CST & TPT 50, then first it will calculate the ED value like X*13.5% ( X = value ) = Y, then CST will be calculated like Y*2% =Z, then TPT : qty*50 = V. Ultimately Total value will come (X + Y + Z + V).

Think Now I am clear.
Regards
RAMU

#### RAMU

Dear friends,

Thanks
RAMU

##### Well-known Member
Dear friends,

Thanks
RAMU
Try this:

And just for future reference, when there are ambiguous calculations in the sheet (like whether taxes are taxed, or if they are all added together and taxed on the value alone), you may want to include samples for those cases, and give us explanations of what you expect the answer to be (it will save us trouble).

#### RAMU

##### Active Member
Dear Friend,

Shall try to remember your suggestion. Your formula works perfect. thanx

RAMU

Replies
2
Views
302
Replies
8
Views
287
Replies
13
Views
607
Replies
5
Views
430
Replies
9
Views
451

1,130,045
Messages
5,639,746
Members
417,108
Latest member
Thein Than

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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