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

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?

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?
>

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

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

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

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