Computing totals for tax and non-tax items

[Dan Wilson]

Good day. I am using Excel 2002 with Windows ME. I have
a worksheet being used as an order form for products to be
ordered. The products are on a separate worksheet and
referenced by VLOOKUP in the order worksheet. I use the
VLOOKUP to use the product number (ex: 810) to get the
part description, cost and tax Code to fill in the order
worksheet. The Tax code is either "t" or "n" ( tax or non-
tax). On the order worksheet, the list of products being
ordered is totalled and the state tax is computed against
this total. My problem is, that some of the products
being ordered are not taxable. Is there a way to total
the non-taxable items and then subtract that total from
the products total so that the tax can be computed against
taxable items only? I am open to modifying the worksheet
to make this easy. The current format is;

A B C D E F

810 2 Widget 1 $45.00 $90.00 t
820 1 Widget 2 $25.00 $25.00 n

Column A = Item Number
Column B = Quantity ordered
Column C = Item Description
Column D = Item Cost
Column E = Total Item Cost
Column F = Tax/Non-Tax

The separation of the taxable and non-taxable items can be
totalled at any point on the worksheet to make this easy.

Thanks, Danno...

 

[Guest]

You will want to create a "Taxable total" cell (e.g. C25) and use SUMIF to
add the "t" items (e.g. =SUMIF(F2:F20,"=T",G2:G20).

Then add a cell (e.g. C26) to calculate the tax based on a tax rate in
another cell (e.g. B26) (e.g. =C25*B26).

Finally, total it all together in G26 (e.g. =SUM(G2:G20)+C26 )

HTH

Bruce

 

[Dan Wilsosn]

Good dary Bruce,

Thanks for the quick response and the excellent help. I
can always count on getting the right answer in this
newsgroup.
Thanks, Danno...