Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF function in "Price quote with tax calculation" templae
able?Hi:
I am trying to modify the "Price quote with tqx calculation" template. We have 2 sales taxes. The first tax is calculated as a % of the total sales+Tax 1. I need to show each tax separately, then total them. The Quote template has a column "Taxable?" (Column E) The tax is calculated only on the items with a T in Column E. Cell F31 is Subtotal: =SUM(F22:F30) Cell F32 is Tax Calculation 1: =7%*SUMIF(E22:E30,"T",F22;F30) Now I need to calculate Tax 2, which is 7.5% of the (Subtotal + Tax1). i.e. I need to add the Subtotal and Tax 1, multiply it by 7.5% and show that result. But, like Tax 1, only for the items with a T in Column E. I am trying the formula: =7.5%*SUMIF(E22:E30,"T",F31:F32) but without success: Any suggestions would be much appreciated. Thank you. -- Peter |
#2
|
|||
|
|||
Peter wrote: able?Hi: I am trying to modify the "Price quote with tqx calculation" template. We have 2 sales taxes. The first tax is calculated as a % of the total sales+Tax 1. I need to show each tax separately, then total them. The Quote template has a column "Taxable?" (Column E) The tax is calculated only on the items with a T in Column E. Cell F31 is Subtotal: =SUM(F22:F30) Cell F32 is Tax Calculation 1: =7%*SUMIF(E22:E30,"T",F22;F30) Now I need to calculate Tax 2, which is 7.5% of the (Subtotal + Tax1). i.e. I need to add the Subtotal and Tax 1, multiply it by 7.5% and show that result. But, like Tax 1, only for the items with a T in Column E. I am trying the formula: =7.5%*SUMIF(E22:E30,"T",F31:F32) but without success: Any suggestions would be much appreciated. Thank you. Is it not... =SUM(F31:F32)*7.5% as your description seems to suggest? |
#3
|
|||
|
|||
Hi Aladin:
Thanks for answering my question. Your solution would be correct, if there is always a tax to calculate. However, the calculation should only be done if there is a T in Column E. Let me try to show an example; my explanation was not too clear: Col D Col E Col F (Price) (Taxable?) Amount Row 22 75 T 75 Row 23 25 T 25 Row 24 10 10 Row 30 - - - Row 31 Subtotal 110.00 (=SUM F22:F30) Row 32 Tax1 7% 7.00 (100*7%; no tax on 10) Row 33 Tax2 7.5% 8.03 (100+7)*7.5% Row 34 Total Tax 15.03 (=SUM F32:F33) Row 35 Total 125.03 (115.03 +10) Formula for Tax1: =7%*SUMIF(E22:E30,"T",F:22:F30) -- This works OK. If there's a T somewhere in column E, it calculates the tax; otherwise, it returns nothing ?Formula for Tax2?: =7.5%*SUMIF(E22:E30,"T",F31:F32) -- This does not work; it always returns a value, even if there is no T in Column E; the value is also calculated on the Subtotal, not on the Subtotal + Tax1 -- Peter "Aladin Akyurek" wrote: Peter wrote: able?Hi: I am trying to modify the "Price quote with tqx calculation" template. We have 2 sales taxes. The first tax is calculated as a % of the total sales+Tax 1. I need to show each tax separately, then total them. The Quote template has a column "Taxable?" (Column E) The tax is calculated only on the items with a T in Column E. Cell F31 is Subtotal: =SUM(F22:F30) Cell F32 is Tax Calculation 1: =7%*SUMIF(E22:E30,"T",F22;F30) Now I need to calculate Tax 2, which is 7.5% of the (Subtotal + Tax1). i.e. I need to add the Subtotal and Tax 1, multiply it by 7.5% and show that result. But, like Tax 1, only for the items with a T in Column E. I am trying the formula: =7.5%*SUMIF(E22:E30,"T",F31:F32) but without success: Any suggestions would be much appreciated. Thank you. Is it not... =SUM(F31:F32)*7.5% as your description seems to suggest? |
#4
|
|||
|
|||
Sorry: the Tabs didn't work well in my Example:
The Col D (Price) should be over the number 75, 25 nd 10 Col E (Taxible) should be over the T's Col F should be over the Second set of 75, 25 and 10. All the calculations and formulae are in Col F. Again, any help is much appreciated. Thanks. -- Peter |
#5
|
|||
|
|||
Your function is looking for a T in the range E22:E30, but summing F31:F32. Change it to =7.5%*SUMIF(E22:E30,"T",F22:F30) Or, if you are really taxing tax, =(7.5%*SUMIF(E22:E30,"T",F22:F30))+(7.5%*F32) -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741 View this thread: http://www.excelforum.com/showthread...hreadid=472385 |
#6
|
|||
|
|||
I see...
E31: Subtotal F31: =SUM(F22:F30) E32: Taxable Subtotal F32: =SUMIF(E22:E30,"T",F22:F30) E33: Tax1 F33: =F32*7% E34: Tax2 F34: =SUM(F32:F33)*7.5% E35: Total Tax F35: =F33+F34 E36: Total F36: =SUM(F32:F34)+F31-F32 Peter wrote: Hi Aladin: Thanks for answering my question. Your solution would be correct, if there is always a tax to calculate. However, the calculation should only be done if there is a T in Column E. Let me try to show an example; my explanation was not too clear: Col D Col E Col F (Price) (Taxable?) Amount Row 22 75 T 75 Row 23 25 T 25 Row 24 10 10 Row 30 - - - Row 31 Subtotal 110.00 (=SUM F22:F30) Row 32 Tax1 7% 7.00 (100*7%; no tax on 10) Row 33 Tax2 7.5% 8.03 (100+7)*7.5% Row 34 Total Tax 15.03 (=SUM F32:F33) Row 35 Total 125.03 (115.03 +10) Formula for Tax1: =7%*SUMIF(E22:E30,"T",F:22:F30) -- This works OK. If there's a T somewhere in column E, it calculates the tax; otherwise, it returns nothing ?Formula for Tax2?: =7.5%*SUMIF(E22:E30,"T",F31:F32) -- This does not work; it always returns a value, even if there is no T in Column E; the value is also calculated on the Subtotal, not on the Subtotal + Tax1 |
#7
|
|||
|
|||
Thanks to both Lillibeth and Aladin for your replies.
Sadly, Tax2 is truly a tax on Tax, so your second formula is the one that does the trick. I guess that what I didn't understand was that the range of cells in both the columns had to be within the same rows for the SUMIF to work. Now, all I need is hundreds of people asking for quotes! Thanks again. -- Peter "Lilliabeth" wrote: Your function is looking for a T in the range E22:E30, but summing F31:F32. Change it to =7.5%*SUMIF(E22:E30,"T",F22:F30) Or, if you are really taxing tax, =(7.5%*SUMIF(E22:E30,"T",F22:F30))+(7.5%*F32) -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741 View this thread: http://www.excelforum.com/showthread...hreadid=472385 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif function with remote cell references | Excel Worksheet Functions | |||
Improve SUMIF function to sum more than one column | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
SUMIF function | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions |