Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Computing totals for tax and non-tax items
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... |
#2
|
|||
|
|||
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 Wilson" wrote: 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... |
#3
|
|||
|
|||
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... -----Original Message----- 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 Wilson" wrote: 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... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|