| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calcution with two conditon
Sir,
I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calcution with two conditon
Total Tax would be...
=SUMPRODUCT((B2:B5="F&B")*E2:E5*14%)+ SUMPRODUCT((B2:B5="Alch Bevrg")*E2:E5*20%) If this post helps click Yes --------------- Jacob Skaria "Rao Ratan Singh" wrote: Sir, I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calcution with two conditon
I assume that the product description is in B Column and the corresponding
amount is in E Column, just past the below formula in Tax Cell. Tax Calculation:- =SUMIF(B:B,"F&B",E:E)*14%+SUMIF(B:B,"Alch Bevrg",E:E)*20% Grand Total:- If the subtotal is in E6 Cell and the Tax is in E7 then use your formula like this =SUM(E6:E7) Or =SUMIF(B:B,"SUBTOTAL",E:E)+SUMIF(B:B,"TAX",E:E) Change the cell reference to your desired cell, if required, If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Rao Ratan Singh" wrote: Sir, I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calcution with two conditon
Try this...
=SUM(SUMIF(B2:B8,{"F&B";"Alchoholic Beverage"},E2:E8)*{0.14;0.2}) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Sir, I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calcution with two conditon
Thank you T. Valko,
I am very much grateful to you. One more thing - I want that it should calculate round means i dont want e.g 2018.80. I wnat 2019.00 I hope you will solve this problem also "T. Valko" wrote: Try this... =SUM(SUMIF(B2:B8,{"F&B";"Alchoholic Beverage"},E2:E8)*{0.14;0.2}) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Sir, I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calcution with two conditon
If you want to round to the nearest whole number:
=ROUND(your_formula,0) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Thank you T. Valko, I am very much grateful to you. One more thing - I want that it should calculate round means i dont want e.g 2018.80. I wnat 2019.00 I hope you will solve this problem also "T. Valko" wrote: Try this... =SUM(SUMIF(B2:B8,{"F&B";"Alchoholic Beverage"},E2:E8)*{0.14;0.2}) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Sir, I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calcution with two conditon
Sir,
I am very thankful to you. But another problem is how to minus discount amount which on Total amount before Tax. Regards RR Singh "T. Valko" wrote: If you want to round to the nearest whole number: =ROUND(your_formula,0) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Thank you T. Valko, I am very much grateful to you. One more thing - I want that it should calculate round means i dont want e.g 2018.80. I wnat 2019.00 I hope you will solve this problem also "T. Valko" wrote: Try this... =SUM(SUMIF(B2:B8,{"F&B";"Alchoholic Beverage"},E2:E8)*{0.14;0.2}) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Sir, I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calcution with two conditon
See if this helps...
B1 = price = 100 B2 = discount = 22.33 B3 = tax = 3.25% Price - discount = 77.67 3.25% tax on 77.67 = 80.14928 Rounded to the nearest whole dollar: =ROUND((B1-B2)*(1+B3),0) = 80 -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Sir, I am very thankful to you. But another problem is how to minus discount amount which on Total amount before Tax. Regards RR Singh "T. Valko" wrote: If you want to round to the nearest whole number: =ROUND(your_formula,0) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Thank you T. Valko, I am very much grateful to you. One more thing - I want that it should calculate round means i dont want e.g 2018.80. I wnat 2019.00 I hope you will solve this problem also "T. Valko" wrote: Try this... =SUM(SUMIF(B2:B8,{"F&B";"Alchoholic Beverage"},E2:E8)*{0.14;0.2}) -- Biff Microsoft Excel MVP "Rao Ratan Singh" wrote in message ... Sir, I am going to a creat sheet for invoice. I want a formula that can calculate taxes with two different rates based on item name. means - when it found in the particular range "F&B" it should be calculate 14% tax on Amount and if it found "Alchoholic Beverage" then it should calculate 20% on amount Format is S.No. Descption Qty Rate Amount 01 F&B 2 150 300 02 Alch Bevrg 6 300 1800 SubTotal 2100 Tax = Grand Total = . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How i count coulm by conditon | Excel Discussion (Misc queries) | |||
| If statement - can it stop once a conditon is reached? | Excel Worksheet Functions | |||
| copying conditon formats with $ | Excel Worksheet Functions | |||
| Get data with certain conditon | Excel Discussion (Misc queries) | |||
| How to determine the number from given conditon? | Excel Discussion (Misc queries) | |||
Linear Mode
