Get value dynamic & sum Excel

[ngocanh87]

Hello,
Sorry for asking too much but I still need support from everyone

I have Invoice sheet with dynamic data not fix columns & rows as att file. How can I get value & sum if (QUANTITY (PC), QUANTITY (KGS), AMOUNT(USD) )the same value.

I see some format at A column + Title row: (MM),(OD X MM) will merge
Ex: Get value C,E,G or add I (all dynamic)
42.7x2.3x774 16,326 27,324 30,693

25x25x1.6x8000 200 1.792 1,756.16

Template INV.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ITEM No.	DESCRIPTION OF GOODS (MM)						BDL	KG/ M	QUANTITY (PC)	QUANTITY (KGS)	U.PRICE (USD/PCS)	AMOUNT (USD)
2
3	CARBON STEEL TUBES										CIF BANGKOK PORT
4	1	Φ	42.7	x	2.3	x	774	92	2.290	8,326	14,758	1.88	15,652.88
5	1	Φ	42.7	x	2.3	x	774	92	2.000	8,000	12,566	1.88	15,040.00
6			42.7	x	2.3	x	774			16,326	27,324		30,693
7	2	Φ	48.6	x	2.4	x	1,900	30	2.730	2,640	13,693	5.28	13,939.20
8	3	Φ	48.6	x	2.4	x	660	45	2.730	4,010	7,225	1.85	7,418.50
9	TOTAL							259		39,302	75,566		82,743.46
10
11	ITEM No.	PO#			SIZE (OD X MM)					PC/ BDL	KG/ M	QUANTITY (PC)	N.WEIGHT (MT)	U.PRICE (USD/MT)	AMOUNT (USD)
12	1	10615	25	x	25	x	1.6	x	8000	100	1.120	200	1.792	980	1,756.16
13	2	10615	30	x	30	x	1.6	x	6500	100	1.380	388	3.480	980	3,410.40
14	3	10615	50	x	50	x	1.6	x	8000	64	2.380	192	3.656	980	3,582.88
15	3	10615	50	x	50	x	1.6	x	8000	64	2.380	192	3.656	980	3,582.88
16	4	10592	100	x	50	x	1.6	x	8000	18	3.630	1,100	31.944	980	31,305.12
17	Total											2,072	44.528		43,637.44
18
19	ITEM No.	PO NO	DISCRIPTION OF GOODS (MM)								KG/M	QUANTITY (PC)	QUANTITY (MT)	U.PRICE (USD/MT)	AMOUNT (USD)
20
21	* ERW CARBON STEEL PIPE
22	1	270722-1	Φ	OD	60.5	MM x WT	4.2	MM x MM/PC	6,150	MM	5.83	592	21.226	1090	23,136.34
23	2	270722-1	Φ	OD	65.0	MM x WT	4.5	MM x MM/PC	6,030	MM	6.71	432	17.478	1090	19,051.02
24	3	290822-1	Φ	OD	76.3	MM x WT	4.8	MM x MM/PC	6,450	MM	8.46	250	13.642	1040	14,187.68
25	4	300522-3	Φ	OD	89.1	MM x WT	4.8	MM x MM/PC	6,050	MM	9.98	328	19.805	1320	26,142.60
26	5	300522-4	Φ	OD	165.2	MM x WT	5.0	MM x MM/PC	6,330	MM	19.8	135	16.921	1360	23,012.56
27	TOTAL											1737	89.072		105,530.20
28
29	ITEM No.	SIZE (OD X MM)				Length	BDL	PC/BDL	KG/M	QUANTITY (PCS)	N.WEIGHT (MT)	U.PRICE (USD/MT)	AMOUNT (USD)
30
31
32	1	(125A)	140	X	4.5	5500	2	10	15.00	20	1.650	1270	2,095.50
33	2	(150A)	165	X	5.0	5500	7	10	19.80	70	7.623	1332	10,153.84
34	3	(200A)	216	X	5.8	5500	18	7	30.10	126	20.859	1332	27,784.19
35		TOTAL:					27			216	30.132		40,033.53
36
37	ITEM No.	DISCRIPTION OF GOODS (MM)							KG/ PCS	QUANTITY (PC)	QUANTITY (KGs)	U.PRICE (USD/PCS)	AMOUNT (USD)
38
39	* JIS CONDUIT EQUIVALENT (EMT PIPE)										CIF CY NORTH MANILA, PHILIPPINES
40	1	E19	F	19.1	x	1.2	x	3,660	1.94	5,306	10,294	2.13	11,301.78
41	2	E25	F	25.4	x	1.2	x	3,660	2.62	2,200	5,764	3.52	7,744.00
42	TOTAL									7,506	16,058		19,045.78
43
44	ITEM No.	DESCRIPTION OF GOODS (MM)							KG/ M	QUANTITY (PC)	QUANTITY (MT)	U.PRICE (USD/PCS)	AMOUNT (USD)
45
46	* RECTANGULAR PIPE										CIF CY NORTH MANILA, PHILIPPINES
47	1	19	x	19	x	1.2	x	2,130	0.642	12,150	16.615	1.38	16,767.00
48	2	19	x	19	x	1.2	x	2,090	0.642	6,975	9.359	1.35	9,416.25
49	3	19	x	19	x	1.2	x	1,721	0.642	7,425	8.204	1.13	8,390.25
50	4	19	x	19	x	1.2	x	1,658	0.642	2,250	2.395	1.09	2,452.50
51	TOTAL									28,800	36.573		37,026.00
52
53	ITEM No.	DISCRIPTION OF GOODS (MM)								KG/PCS	QUANTITY (PC)	QUANTITY (KGS)	U.PRICE (USD/PCS)	AMOUNT (USD)
54
55	1	23.42	mm	x	1.245	mm	x	3048	mm	2.07	12,000	24.840	3.74	44,880.00
56	TOTAL										12,000	24.840		44,880.00
57
58	SIZE (mm)					QUANTITY (Pcs.)	WEIGHT (Kgs)	PRICE (per pc)	AMOUNT (USD)
59	COMMERCIAL QUALITY EQUIVALENT TO BS31:1940 CLASS_B, IN CUT LENGTH OF 10FT, CLASS3
60	19.05	x	1.63	x	3048	11,100	24123	@US$3.04	33,744.00
61	25.4	x	1.63	x	3048	4,600	13,628	@US$4.14	19,044.00
62	TOTAL					15,700 Pcs.	37,751		52,788.00
Sheet1

Cell Formulas
Range	Formula
F62,M42,J42:K42,M6,J6:K6	J6	=SUM(J4:J5)
H9,O27,L27:M27,O17,L17:M17,M9,J9:K9	H9	=SUM(H4:H8)
M7:M8,M4:M5	M4	=ROUND(L4*J4,2)
M32:M34,O22:O26,O12:O16	O12	=ROUND(M12*N12,2)
J32:J34	J32	=G32*H32
G35,M35,J35:K35	G35	=SUM(G32:G34)
M40:M41	M40	=J40*L40
M51,J51:K51	J51	=SUM(J47:J50)
N55,M47:M50	M47	=ROUND(J47*L47,2)
N56,K56:L56	K56	=SUM(K55:K55)
G62,I62	G62	=SUBTOTAL(9,G60:G61)
I60:I61	I60	=H60*F60

 

[nemmi69]

The trouble with a dynamic range is that if you put a sum in its column you cause a circular reference.
Example my dynamic range is Drange in column A