SUMIF function in "Price quote with tax calculation" templae

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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.
Click to expand...

Is it not...

=SUM(F31:F32)*7.5%

as your description seems to suggest?
 
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
 
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.
 
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)
 
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
 
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.
 
Back
Top