Need help in Pivot Table

[mehidy1437]

Hello There,

Just started to learn the pivot table. While playing around to understand it. I have this problem.
While transforming some data, columns to rows, duplicate values are not merging & not showing the correct sum. I have highlighted the cell in yellow.

Pivot Table:

EXAMPLE.xlsx
	A	B	C	D	E
3	ART	PO	COL	Values
4	WTRT1	22	B94	Sum of L
5	WTRT1	22	B94	Sum of M
6	WTRT1	22	B94	Sum of S	200
7	WTRT1	22	B94	Sum of XL
8	WTRT1	22	B94	Sum of XXL
9	WTRT1	22	B94	Sum of XS	100
10	WTRT1	23	B94	Sum of L
11	WTRT1	23	B94	Sum of M
12	WTRT1	23	B94	Sum of S
13	WTRT1	23	B94	Sum of XL	500
14	WTRT1	23	B94	Sum of XXL
15	WTRT1	23	B94	Sum of XS
16	WTRT1	23	E100	Sum of L	400
17	WTRT1	23	E100	Sum of M
18	WTRT1	23	E100	Sum of S
19	WTRT1	23	E100	Sum of XL
20	WTRT1	23	E100	Sum of XXL
21	WTRT1	23	E100	Sum of XS	600
22	WTRT1	24	C94	Sum of L	1200
23	WTRT1	24	C94	Sum of M
24	WTRT1	24	C94	Sum of S
25	WTRT1	24	C94	Sum of XL
26	WTRT1	24	C94	Sum of XXL
27	WTRT1	24	C94	Sum of XS
28	WTRT1	25	B94	Sum of L	900
29	WTRT1	25	B94	Sum of M
30	WTRT1	25	B94	Sum of S
31	WTRT1	25	B94	Sum of XL
32	WTRT1	25	B94	Sum of XXL
33	WTRT1	25	B94	Sum of XS
34	WTRTB	22	B94	Sum of L
35	WTRTB	22	B94	Sum of M
36	WTRTB	22	B94	Sum of S	300
37	WTRTB	22	B94	Sum of XL
38	WTRTB	22	B94	Sum of XXL
39	WTRTB	22	B94	Sum of XS
40	WTRTB	23	E100	Sum of L	700
41	WTRTB	23	E100	Sum of M
42	WTRTB	23	E100	Sum of S
43	WTRTB	23	E100	Sum of XL
44	WTRTB	23	E100	Sum of XXL
45	WTRTB	23	E100	Sum of XS
46	WTRTB	24	F200	Sum of L
47	WTRTB	24	F200	Sum of M	800
48	WTRTB	24	F200	Sum of S
49	WTRTB	24	F200	Sum of XL
50	WTRTB	24	F200	Sum of XXL
51	WTRTB	24	F200	Sum of XS
52	WTRTC	25	D94	Sum of L
53	WTRTC	25	D94	Sum of M
54	WTRTC	25	D94	Sum of S
55	WTRTC	25	D94	Sum of XL	1300
56	WTRTC	25	D94	Sum of XXL
57	WTRTC	25	D94	Sum of XS
58	WTRTC	25	F200	Sum of L
59	WTRTC	25	F200	Sum of M	1100
60	WTRTC	25	F200	Sum of S	1000
61	WTRTC	25	F200	Sum of XL
62	WTRTC	25	F200	Sum of XXL
63	WTRTC	25	F200	Sum of XS
64	WTRTD	26	D94	Sum of L
65	WTRTD	26	D94	Sum of M
66	WTRTD	26	D94	Sum of S
67	WTRTD	26	D94	Sum of XL
68	WTRTD	26	D94	Sum of XXL	1400
69	WTRTD	26	D94	Sum of XS
70	WTRTD	26	E94	Sum of L
71	WTRTD	26	E94	Sum of M
72	WTRTD	26	E94	Sum of S
73	WTRTD	26	E94	Sum of XL	1500
74	WTRTD	26	E94	Sum of XXL
75	WTRTD	26	E94	Sum of XS
76	WTRTD	(blank)	D94	Sum of L
77	WTRTD	(blank)	D94	Sum of M
78	WTRTD	(blank)	D94	Sum of S
79	WTRTD	(blank)	D94	Sum of XL	1700
80	WTRTD	(blank)	D94	Sum of XXL	1600
81	WTRTD	(blank)	D94	Sum of XS
82	Total Sum of L				3200
83	Total Sum of M				1900
84	Total Sum of S				1500
85	Total Sum of XL				5000
86	Total Sum of XXL				3000
87	Total Sum of XS				700
Sheet10

Source Data:

EXAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J
2	ART	COL	PO	L	M	S	XL	XS	XXL	Grand Total
3	WTRT1	B94	22			200		100		300
4	WTRT1	B94	22			200		100		300
5	WTRT1	B94	23				500			500
6	WTRT1	B94	25	900						900
7	WTRT1	C94	24	1200						1200
8	WTRT1	E100	23	400				600		1000
9	WTRTB	B94	22			300				300
10	WTRTB	E100	23	700						700
11	WTRTB	F200	24		800					800
12	WTRTC	D94	25				1300			1300
13	WTRTC	F200	25		1100	1000				2100
14	WTRTD	D94	26						1400	1400
15	WTRTD	D94	(blank)				1700		1600	3300
16	WTRTD	E94	26				1500			1500
17				3200	1900	1700	5000	800	3000	15600
Sheet6

Cell Formulas
Range	Formula
J3:J17	J3	=SUM(D3:I3)
D17:I17	D17	=SUM(D3:D16)

 

[mehidy1437]

BUMP

 

[mehidy1437]

After clicking on the below option, I'm getting the merging values total sum.

EXAMPLE.xlsx
	A	B	C	D	E
1
2
3	ART	PO	COL	Values	Total
4	WTRT1	22	B94	Sum of L
5	WTRT1	22	B94	Sum of M
6	WTRT1	22	B94	Sum of S	400
7	WTRT1	22	B94	Sum of XL
8	WTRT1	22	B94	Sum of XS	200
9	WTRT1	22	B94	Sum of XXL
10	WTRT1	23	B94	Sum of L
11	WTRT1	23	B94	Sum of M
12	WTRT1	23	B94	Sum of S
13	WTRT1	23	B94	Sum of XL	500
14	WTRT1	23	B94	Sum of XS
15	WTRT1	23	B94	Sum of XXL
16	WTRT1	23	E100	Sum of L	400
17	WTRT1	23	E100	Sum of M
18	WTRT1	23	E100	Sum of S
19	WTRT1	23	E100	Sum of XL
20	WTRT1	23	E100	Sum of XS	600
21	WTRT1	23	E100	Sum of XXL
22	WTRT1	24	C94	Sum of L	1200
23	WTRT1	24	C94	Sum of M
24	WTRT1	24	C94	Sum of S
25	WTRT1	24	C94	Sum of XL
26	WTRT1	24	C94	Sum of XS
27	WTRT1	24	C94	Sum of XXL
28	WTRT1	25	B94	Sum of L	900
29	WTRT1	25	B94	Sum of M
30	WTRT1	25	B94	Sum of S
31	WTRT1	25	B94	Sum of XL
32	WTRT1	25	B94	Sum of XS
33	WTRT1	25	B94	Sum of XXL
34	WTRTB	22	B94	Sum of L
35	WTRTB	22	B94	Sum of M
36	WTRTB	22	B94	Sum of S	300
37	WTRTB	22	B94	Sum of XL
38	WTRTB	22	B94	Sum of XS
39	WTRTB	22	B94	Sum of XXL
40	WTRTB	23	E100	Sum of L	700
41	WTRTB	23	E100	Sum of M
42	WTRTB	23	E100	Sum of S
43	WTRTB	23	E100	Sum of XL
44	WTRTB	23	E100	Sum of XS
45	WTRTB	23	E100	Sum of XXL
46	WTRTB	24	F200	Sum of L
47	WTRTB	24	F200	Sum of M	800
48	WTRTB	24	F200	Sum of S
49	WTRTB	24	F200	Sum of XL
50	WTRTB	24	F200	Sum of XS
51	WTRTB	24	F200	Sum of XXL
52	WTRTC	25	D94	Sum of L
53	WTRTC	25	D94	Sum of M
54	WTRTC	25	D94	Sum of S
55	WTRTC	25	D94	Sum of XL	1300
56	WTRTC	25	D94	Sum of XS
57	WTRTC	25	D94	Sum of XXL
58	WTRTC	25	F200	Sum of L
59	WTRTC	25	F200	Sum of M	1100
60	WTRTC	25	F200	Sum of S	1000
61	WTRTC	25	F200	Sum of XL
62	WTRTC	25	F200	Sum of XS
63	WTRTC	25	F200	Sum of XXL
64	WTRTD	(blank)	D94	Sum of L
65	WTRTD	(blank)	D94	Sum of M
66	WTRTD	(blank)	D94	Sum of S
67	WTRTD	(blank)	D94	Sum of XL	1700
68	WTRTD	(blank)	D94	Sum of XS
69	WTRTD	(blank)	D94	Sum of XXL	1600
70	WTRTD	26	D94	Sum of L
71	WTRTD	26	D94	Sum of M
72	WTRTD	26	D94	Sum of S
73	WTRTD	26	D94	Sum of XL
74	WTRTD	26	D94	Sum of XS
75	WTRTD	26	D94	Sum of XXL	1400
76	WTRTD	26	E94	Sum of L
77	WTRTD	26	E94	Sum of M
78	WTRTD	26	E94	Sum of S
79	WTRTD	26	E94	Sum of XL	1500
80	WTRTD	26	E94	Sum of XS
81	WTRTD	26	E94	Sum of XXL
82	Total Sum of L				3200
83	Total Sum of M				1900
84	Total Sum of S				1700
85	Total Sum of XL				5000
86	Total Sum of XS				800
87	Total Sum of XXL				3000
Sheet5