gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 660
- Office Version
- 2019
- Platform
- Windows
Hello,
Im using the following array: (A25)=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$25:A25, $B$9:$B$20), 0)),"")
Works fine, I just want to add 1 criteria, if cells N9:N20 = "sold" then perform the array.
Thank you.
Im using the following array: (A25)=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$25:A25, $B$9:$B$20), 0)),"")
Works fine, I just want to add 1 criteria, if cells N9:N20 = "sold" then perform the array.
Thank you.
The Whole Enchilada.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
23 | Total +/- per Sector | ||||||
24 | Sector | Gain | Stock % | Sector % | Count | ||
25 | Tech Srvc | $ (18,640.00) | -57.53% | -54.53% | 4 | ||
26 | Elec Tech | $ (4,180.00) | -43.18% | 107.12% | 1 | ||
27 | Retail | $ (1,650.00) | -1.91% | 81.45% | 1 | ||
28 | 0 | $ - | |||||
29 | $ - | ||||||
30 | $ - | ||||||
31 | $ - | ||||||
32 | $ - | ||||||
33 | $ - | ||||||
34 | Total: | $ (24,470.00) | -34.20% | 94.29% | 6 | ||
Option Monster |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B25:B33 | B25 | =SUMIFS($K$9:$K$20,$B$9:$B$20,A25) |
C25:C33 | C25 | =IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A25),"") |
D25:D33 | D25 | =IFERROR(VLOOKUP($A25,'Sector Perform-ALL'!$G$3:$H$24,2,0),"") |
E25:E33 | E25 | =IF(A25=0,"",IF($A25="","",COUNTIF(B:B,$A25))) |
B34,E34 | B34 | =SUM(B25:B33) |
C34 | C34 | =IFERROR(AVERAGE(C$24:C$32),"") |
D34 | D34 | =AVERAGEIF(D25:D33,">0") |
A25:A33 | A25 | =IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$25:A25, $B$9:$B$20), 0)),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |