Calcution with two conditon

[Rao Ratan Singh]

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 =

 

[Jacob Skaria]

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

 

[Ms-Exl-Learner]

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!

 

[T. Valko]

Try this...

=SUM(SUMIF(B2:B8,{"F&B";"Alchoholic Beverage"},E2:E8)*{0.14;0.2})

 

[Rao Ratan Singh]

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]

If you want to round to the nearest whole number:

=ROUND(your_formula,0)

 

[Rao Ratan Singh]

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]

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