Long Code, Sum I

[Freeman5438]

I need help, I have a Quotation that I have made for work. It has drop downs Lists to save time and speed up the completion of the quotation.

Problem is I have added a drop down List under subtotal is for offers like 20% off ect.

the one I'm having trouble with is the free packing material List.

I need to count all the packing material only and add this up before taking it away from the grand total.

[TABLE="width: 500"]
<tbody>[TR]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]QUANTITY[/FONT]<strike></strike>
[/TD]
[TD]<strike></strike>[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]DESCRIPTION[/FONT]<strike></strike>
[/TD]
[TD]<strike></strike>[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]UNIT PRICE[/FONT]<strike></strike>
[/TD]
[TD]<strike></strike>[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]AMOUNT[/FONT]<strike></strike>
[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Small Box 457x305x254mm[/FONT]<strike></strike>
[/TD]
[TD]1.30[/TD]
[TD]44.20[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Medium Box 457x457x457mm[/FONT]<strike></strike>
[/TD]
[TD]1.90[/TD]
[TD]32.30[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Rental Service, Wardrobe Box 508x457x1016mm[/FONT]<strike></strike>
[/TD]
[TD]3.00[/TD]
[TD]18.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Large Box 457x457x914mm[/FONT]<strike></strike>
[/TD]
[TD]2.60[/TD]
[TD]15.60[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Small Box 457x305x254mm[/FONT]<strike></strike>
[/TD]
[TD]1.30[/TD]
[TD]7.80[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Medium Box 457x457x457mm[/FONT]<strike></strike>
[/TD]
[TD]1.90[/TD]
[TD]9.50[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Full Packing Service[/FONT]<strike></strike>
[/TD]
[TD]500.00[/TD]
[TD]500.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dismantling & Reassembling Service[/FONT]<strike></strike>
[/TD]
[TD]25.00[/TD]
[TD]75.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Small Box 457x305x254mm[/FONT]<strike></strike>
[/TD]
[TD]1.30[/TD]
[TD]1.30[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Medium Box 457x457x457mm[/FONT]<strike></strike>
[/TD]
[TD]1.90[/TD]
[TD]1.90[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Loading, Unloading & Moving Service[/FONT]<strike></strike>
[/TD]
[TD]1000.00[/TD]
[TD]1000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]SUBTOTAL[/FONT]<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Free Packing Material [/FONT]<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]TOTAL[/FONT]<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

(a) how do you identify "packing material" and (b) what gets removed from the subtotal?

 

[kweaver]

If I understand you correctly, I used a helper column E which could be hidden, then:

Excel 2010
	A	B	C	D	E
1	QUANTITY	DESCRIPTION	UNIT PRICE	AMOUNT	Packing
2	34	Small Box 457x305x254mm	$1.30	$44.20	44.2
3	17	Medium Box 457x457x457mm	$1.90	$32.30	32.3
4	6	Rental Service, Wardrobe Box 508x457x1016mm	$3.00	$18.00	18
5	6	Large Box 457x457x914mm	$2.60	$15.60	15.6
6	6	Small Box 457x305x254mm	$1.30	$7.80	7.8
7	5	Medium Box 457x457x457mm	$1.90	$9.50	9.5
8	1	Full Packing Service	$500.00	$500.00
9	3	Dismantling & Reassembling Service	$25.00	$75.00
10	1	Small Box 457x305x254mm	$1.30	$1.30	1.3
11	1	Medium Box 457x457x457mm	$1.90	$1.90	1.9
12	1	Loading, Unloading & Moving Service	$1,000.00	$1,000.00
13
14			Subtotal	$1,705.60
15			Free Packing Material	$130.60
16			TOTAL	$1,575.00
Sheet9

Cell Formulas
Range	Formula
D2	=A2*C2
D14	=SUM(D2:D12)
E2	=IF(ISNUMBER(FIND("Box",B2)),D2,"")

 

[kweaver]

Actually, D15 is: =IF("Free Packing Material"=C15,SUM(E2:E12),D14*C15)

And the drop-downs for C15 used a list containing Free Packing Material, 10%, 20%...or whatever.

I assumed that any description with "box" in it was packing material.

 

[Freeman5438]

Actually, D15 is: =IF("Free Packing Material"=C15,SUM(E2:E12),D14*C15)

And the drop-downs for C15 used a list containing Free Packing Material, 10%, 20%...or whatever.

I assumed that any description with "box" in it was packing material.

Hi Kweaver, im new to this so how can i send you the workbook about i dont know if im explaning it correctly. It sorted work but i dont need the extra column.

 

[kweaver]

You an private message (PM) by clicking on my name.