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.

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

<tbody>
</tbody>

 

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