Calcution with two conditon

R

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 =
 
J

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
 
M

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!
 
R

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
 
R

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top