# Correction needed in formula INDEX AGGREGATE

#### mehidy1437

##### Active Member
Hello There,
Need correction in formula at J2, it's not giving the correct output considering the header J1:O1

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)

### 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
You need to replace this
COLUMNS(\$A1:A1)
with 1

#### mehidy1437

##### Active Member
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
That's a totally different question, so needs a new thread. Thanks

Replies
9
Views
118
Replies
0
Views
71
Replies
4
Views
47
Replies
5
Views
161
Replies
2
Views
116