Correction needed in formula INDEX AGGREGATE

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
257
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
ABCDEFGHIJKLMNOPQ
1ARTPOCOLSIZEQTYARTPOCOLXSSLXLMXXL TTL
2WTRT122B94XS1001WTRT122B94100     100
3WTRT122B94S2002         0
4WTRTB22B94S3001WTRTB22B94      0
5WTRT123E100L4001WTRT123E100600     600
6WTRT123B94XL5001WTRT123B94      0
7WTRT123E100XS6002         0
8WTRTB23E100L7001WTRTB23E100      0
9WTRTB24F200M8001WTRTB24F200      0
10WTRT125B94L9001WTRT125B94      0
11WTRTC25F200S10001WTRTC25F200      0
12WTRTC25F200M11002         0
13WTRT124C94L12001WTRT124C94      0
14WTRTC25D94XL13001WTRTC25D94      0
15WTRTD26D94XXL14001WTRTD26D94      0
16WTRTD26E94XL15001WTRTD26E94      0
17WTRTD-D94XXL16001WTRTD-D94      0
18WTRTD-D94XL17002         0
Sheet2 (2)
Cell Formulas
RangeFormula
J1:P1J1=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
F2:F18F2=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)
G2:I18G2=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)<=1,A2,"")
J2:O18J2=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:Q18Q2=SUM(J2:P2)
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
You need to replace this
COLUMNS($A1:A1)
with 1
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
257
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
ABCDEFGHIJKLMNOPQ
1ARTPOCOLSIZEQTYARTPOCOLXSSLXLMXXL TTL
2WTRT122B94XS1001WTRT122B94100200    300
3WTRT122B94S2002         0
4WTRTB22B94S3001WTRTB22B94 300    300
5WTRT123E100L4001WTRT123E100600 400   1000
6WTRT123B94XL5001WTRT123B94   500  500
7WTRT123E100XS6002         0
8WTRTB23E100L7001WTRTB23E100  700   700
9WTRTB24F200M8001WTRTB24F200    800 800
10WTRT125B94L9001WTRT125B94  900   900
11WTRTC25F200S10001WTRTC25F200 1000  1100 2100
12WTRTC25F200M11002         0
13WTRT124C94L12001WTRT124C94  1200   1200
14WTRTC25D94XL13001WTRTC25D94   1300  1300
15WTRTD26D94XXL14001WTRTD26D94     14001400
16WTRTD26E94XL15001WTRTD26E94   1500  1500
17WTRTD-D94XXL16001WTRTD-D94   1700 16003300
18WTRTD-D94XL17002         15300
1915300
Sheet2 (3)
Cell Formulas
RangeFormula
J1:P1J1=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
F2:F18F2=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)
G2:I18G2=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)<=1,A2,"")
J2:O17J2=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:O18J18=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:Q17Q2=SUM(J2:P2)
Q18Q18=SUM(Q2:Q17)
E19E19=SUM(E2:E18)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
That's a totally different question, so needs a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,362
Members
412,320
Latest member
sixnine0312
Top