Correction needed in formula INDEX AGGREGATE

[mehidy1437]

Hello There,
Need correction in formula at J2, it's not giving the correct output considering the header J1:O1
Please help me to sort it out, thanks.

EXAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ART	PO	COL	SIZE	QTY		ART	PO	COL	XS	S	L	XL	M	XXL		TTL
2	WTRT1	22	B94	XS	100	1	WTRT1	22	B94	100							100
3	WTRT1	22	B94	S	200	2											0
4	WTRTB	22	B94	S	300	1	WTRTB	22	B94								0
5	WTRT1	23	E100	L	400	1	WTRT1	23	E100	600							600
6	WTRT1	23	B94	XL	500	1	WTRT1	23	B94								0
7	WTRT1	23	E100	XS	600	2											0
8	WTRTB	23	E100	L	700	1	WTRTB	23	E100								0
9	WTRTB	24	F200	M	800	1	WTRTB	24	F200								0
10	WTRT1	25	B94	L	900	1	WTRT1	25	B94								0
11	WTRTC	25	F200	S	1000	1	WTRTC	25	F200								0
12	WTRTC	25	F200	M	1100	2											0
13	WTRT1	24	C94	L	1200	1	WTRT1	24	C94								0
14	WTRTC	25	D94	XL	1300	1	WTRTC	25	D94								0
15	WTRTD	26	D94	XXL	1400	1	WTRTD	26	D94								0
16	WTRTD	26	E94	XL	1500	1	WTRTD	26	E94								0
17	WTRTD	-	D94	XXL	1600	1	WTRTD	-	D94								0
18	WTRTD	-	D94	XL	1700	2											0
Sheet2 (2)

Cell Formulas
Range	Formula
J1:P1	J1	=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
F2:F18	F2	=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)
G2:I18	G2	=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)<=1,A2,"")
J2:O18	J2	=IFERROR(INDEX($E$2:$E$160,AGGREGATE(15,6,(ROW($A$2:$A$160)-ROW($A$2)+1)/($A$2:$A$160=$G2)/($B$2:$B$160=$H2)/($C$2:$C$160=$I2)/($D$2:$D$160=J$1),COLUMNS($A1:A1))),"")
Q2:Q18	Q2	=SUM(J2:P2)

 

[Fluff]

You need to replace this
COLUMNS($A1:A1)
with 1

 

[mehidy1437]

Thank you very much, you are great as always.

In addition, how to remove the blank cell for column G, H & I, any alternative formula?

EXAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ART	PO	COL	SIZE	QTY		ART	PO	COL	XS	S	L	XL	M	XXL		TTL
2	WTRT1	22	B94	XS	100	1	WTRT1	22	B94	100	200						300
3	WTRT1	22	B94	S	200	2											0
4	WTRTB	22	B94	S	300	1	WTRTB	22	B94		300						300
5	WTRT1	23	E100	L	400	1	WTRT1	23	E100	600		400					1000
6	WTRT1	23	B94	XL	500	1	WTRT1	23	B94				500				500
7	WTRT1	23	E100	XS	600	2											0
8	WTRTB	23	E100	L	700	1	WTRTB	23	E100			700					700
9	WTRTB	24	F200	M	800	1	WTRTB	24	F200					800			800
10	WTRT1	25	B94	L	900	1	WTRT1	25	B94			900					900
11	WTRTC	25	F200	S	1000	1	WTRTC	25	F200		1000			1100			2100
12	WTRTC	25	F200	M	1100	2											0
13	WTRT1	24	C94	L	1200	1	WTRT1	24	C94			1200					1200
14	WTRTC	25	D94	XL	1300	1	WTRTC	25	D94				1300				1300
15	WTRTD	26	D94	XXL	1400	1	WTRTD	26	D94						1400		1400
16	WTRTD	26	E94	XL	1500	1	WTRTD	26	E94				1500				1500
17	WTRTD	-	D94	XXL	1600	1	WTRTD	-	D94				1700		1600		3300
18	WTRTD	-	D94	XL	1700	2											15300
19					15300
Sheet2 (3)

Cell Formulas
Range	Formula
J1:P1	J1	=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
F2:F18	F2	=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)
G2:I18	G2	=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)<=1,A2,"")
J2:O17	J2	=IFERROR(INDEX($E$2:$E$160,AGGREGATE(15,6,(ROW($A$2:$A$160)-ROW($A$2)+1)/($A$2:$A$160=$G2)/($B$2:$B$160=$H2)/($C$2:$C$160=$I2)/($D$2:$D$160=J$1),1)),"")
J18:O18	J18	=IFERROR(INDEX($E$2:$E$160,AGGREGATE(15,6,(ROW($A$2:$A$160)-ROW($A$2)+1)/($A$2:$A$160=$G18)/($B$2:$B$160=$H18)/($C$2:$C$160=$I18)/($D$2:$D$160=J$1),COLUMNS($A17:A17))),"")
Q2:Q17	Q2	=SUM(J2:P2)
Q18	Q18	=SUM(Q2:Q17)
E19	E19	=SUM(E2:E18)

 

[Fluff]

That's a totally different question, so needs a new thread. Thanks