K13=SUM(IFERROR((D13=$C$2:$K$2)/(E13=$C$3:$K$3)*$C$4:$K$9,""))
F13=AGGREGATE(9,6,(D13=$C$2:$K$2)/(E13=$C$3:$K$3)*$C$4:$K$9,6)
F13=AGGREGATE(9,6,(D13=$C$2:$K$2)/(E13=$C$3:$K$3)*$C$4:$K$9,6)
Book1.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Style | unit | 1002/NP | 1006/AT | 1009/NB | 1010/LA | 1012/SF | 1014/WC | 1019/MA | 1020/BN | 7088/DIR | ||
2 | 7079 | 7099 | 7099 | 7077 | 7077 | 7099 | 7099 | 7099 | 7088 | ||||
3 | 1002 | 1006 | 1009 | 1010 | 1012 | 1014 | 1019 | 1020 | 7088 | ||||
4 | 1 | 75 | 8 | 8 | 7 | 12 | 8 | 8 | 8 | 7 | 9 | ||
5 | 2 | 70 | 8 | 7 | 7 | 10 | 8 | 7 | 7 | 7 | 9 | ||
6 | 3 | 65 | 7 | 7 | 6 | 9 | 7 | 7 | 7 | 6 | 9 | ||
7 | 4 | 70 | 8 | 7 | 10 | 7 | 7 | 7 | 7 | 10 | |||
8 | 5 | 35 | 8 | 10 | 8 | 9 | |||||||
9 | 6 | 35 | 8 | 7 | 10 | 8 | 9 | ||||||
10 | |||||||||||||
11 | |||||||||||||
12 | DC | Store | Total# Styles by Store | Total# Units by Store | Total# Units by Dc | ||||||||
13 | 1010/LA | 7077 | 1010 | 61 | #VALUE! | ||||||||
14 | 1012/SF | 7077 | 1012 | 46 | 107 | ||||||||
15 | 1002/NP | 7079 | 1002 | 47 | 47 | ||||||||
16 | 7088/DIR | 7088 | 7088 | 55 | 55 | ||||||||
17 | 1006/AT | 7099 | 1006 | 29 | |||||||||
18 | 1009/NB | 7099 | 1009 | 27 | |||||||||
19 | 1014/WC | 7099 | 1014 | 29 | |||||||||
20 | 1019/MA | 7099 | 1019 | 29 | |||||||||
21 | 1020/BN | 7099 | 1020 | 27 | 141 | ||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C13:C21 | C13 | =INDEX($1:$1,AGGREGATE(15,6,COLUMN($C$1:$K$1)/($C$2:$K$2=D13),COUNTIF(D$13:D13,D13))) |
D13:D21 | D13 | =AGGREGATE(15,6,$C$2:$K$2,ROWS($D$13:D13)) |
E13:E21 | E13 | =INDEX($3:$3,AGGREGATE(15,6,COLUMN($C$3:$K$3)/($C$2:$K$2=D13),COUNTIF(D$13:D13,D13))) |
F13:F21 | F13 | =SUM(IFERROR((D13=$C$2:$K$2)/(E13=$C$3:$K$3)*$C$4:$K$9,"")) |
G21,G13:G19 | G13 | =IF(COUNTIF($D$13:$D$21,D13)=COUNTIF($D$13:D13,D13),SUM(IFERROR((D13=$C$2:$K$2)*$C$4:$K$9,"")),"") |
G20 | G20 | =IF(COUNTIF($D$13:$D$21,D20)=COUNTIF($D$13:D20,D20),SUM(IFERROR((D20=$C$2:$K$2)*$C$4:$K$9,"")),"") |
K13 | K13 | =AGGREGATE(9,6,(D13=$C$2:$K$2)/(E13=$C$3:$K$3)*$C$4:$K$9,6) |
Press CTRL+SHIFT+ENTER to enter array formulas. |