# Index, Match, CountIf with 1 criteria

#### gtd526

##### Active Member
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.

Cell Formulas
RangeFormula
B25:B33B25=SUMIFS(\$K\$9:\$K\$20,\$B\$9:\$B\$20,A25)
C25:C33C25=IFERROR(AVERAGEIFS(\$L\$9:\$L\$20,\$B\$9:\$B\$20,\$A25),"")
D25:D33D25=IFERROR(VLOOKUP(\$A25,'Sector Perform-ALL'!\$G\$3:\$H\$24,2,0),"")
E25:E33E25=IF(A25=0,"",IF(\$A25="","",COUNTIF(B:B,\$A25)))
B34,E34B34=SUM(B25:B33)
C34C34=IFERROR(AVERAGE(C\$24:C\$32),"")
D34D34=AVERAGEIF(D25:D33,">0")
A25:A33A25=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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### gaz_chops

##### Well-known Member
Do all cells need to say "Sold" or just 1?

If just 1, then try

=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(0, IF(COUNTIF(\$N\$9:\$N\$20,"Sold"),COUNTIF(\$A\$25:A25, \$B\$9:\$B\$20)), 0)),"")

=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(, IF(COUNTIF(\$N\$9:\$N\$20,"Sold")=12,COUNTIF(\$A\$25:A25, \$B\$9:\$B\$20)), 0)),"")

#### gtd526

##### Active Member
Do all cells need to say "Sold" or just 1?

If just 1, then try

=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(0, IF(COUNTIF(\$N\$9:\$N\$20,"Sold"),COUNTIF(\$A\$25:A25, \$B\$9:\$B\$20)), 0)),"")

=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(, IF(COUNTIF(\$N\$9:\$N\$20,"Sold")=12,COUNTIF(\$A\$25:A25, \$B\$9:\$B\$20)), 0)),"")
"sold" could be in random cells. Will be between 1 and 12 times. I just want the criteria IF N9 thru N12(any cell between)= "sold", perform the array.

FGHIJ
23Sold Options +/- %
24SectorGainStock %Sector %Count
25Elec Tech \$ (4,180.00)-43.18%107.12%1
26Tech Srvc \$ (18,640.00)-56.78%-54.53%4
27Retail \$ (1,650.00)-1.91%81.45%1
280 \$ -
29  \$ -
30  \$ -
Option Monster
Cell Formulas
RangeFormula
F25:F30F25=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(0, IF(COUNTIF(\$N\$9:\$N\$20,"Sold"),COUNTIF(\$F\$25:F25, \$B\$9:\$B\$20)), 0)),"")
G25:G30G25=IFERROR(SUMIFS(\$K\$9:\$K\$20,\$B\$9:\$B\$20,\$F25),"")
H25H25=IFERROR(AVERAGEIFS(\$L\$9:\$L\$20,\$B\$9:\$B\$20,\$F25),"")
I25:I30I25=IFERROR(VLOOKUP(\$F25,'Sector Perform-ALL'!\$G\$3:\$H\$24,2,0),"")
J25:J30J25=IF(\$F25=0,"",IF(\$F25="","",COUNTIF(B:B,\$F25)))
H26:H30H26=IFERROR(VLOOKUP(\$F26,\$B\$9:\$L\$20,11,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

#### gtd526

##### Active Member
Do all cells need to say "Sold" or just 1?

If just 1, then try

=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(0, IF(COUNTIF(\$N\$9:\$N\$20,"Sold"),COUNTIF(\$A\$25:A25, \$B\$9:\$B\$20)), 0)),"")

=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(, IF(COUNTIF(\$N\$9:\$N\$20,"Sold")=12,COUNTIF(\$A\$25:A25, \$B\$9:\$B\$20)), 0)),"")
I tried the "just 1" and it shows non-sold values.
"sold" will be in random cells. Between 1 and 12 times. I just want the criteria IF N9 thru N12(any cell between)= "sold", perform the array.
I tried the above for just 1, and its showing Elec Tech and 1 extra Tech Srvc, which DO NOT show "sold" in N Column.

ABCDEFGHIJKLMNO
7OPTION MONSTEROPTION MONSTER
8 Symbol Sector Type Shares Cost CURRENT MAX Value MIN Value Cost ValueMarket ValueGain/LossG/L %Weight(%)ExpirationStrike
9RNGTech SrvcCall10 \$ 15.27 \$ 6.60 \$ 31.20 \$ 5.00 \$ 15,270.00 \$ 6,600.00 \$ (8,670.00)-56.78%12%7/31270.00
10NVDAElec TechCall10 \$ 9.68 \$ 5.50 \$ 16.05 \$ 5.50 \$ 9,680.00 \$ 5,500.00 \$ (4,180.00)-43.18%8%7/31417.50
11DOCUTech SrvcCall5 \$ 10.45 \$ 3.45 \$ 10.60 \$ 3.45 \$ 5,225.00 \$ 1,725.00 \$ (3,500.00)-66.99%4%sold202.50
12ADSKTech SrvcCall10 \$ 6.18 \$ 3.10 \$ 8.30 \$ 3.10 \$ 6,180.00 \$ 3,100.00 \$ (3,080.00)-49.84%5%sold245.00
13WDAYTech SrvcCall10 \$ 6.00 \$ 2.61 \$ 6.00 \$ 2.61 \$ 6,000.00 \$ 2,610.00 \$ (3,390.00)-56.50%5%sold192.50
14AMZNRetailCall5 \$ 173.00 \$ 169.70 \$ 173.00 \$ 167.65 \$ 86,500.00 \$ 84,850.00 \$ (1,650.00)-1.91%67%sold2,995.00
Option Monster
Cell Formulas
RangeFormula
G9:G14G9=IF(\$F9=0,0,MAX(\$F9,\$G9))
H9:H14H9=IF(\$H9=0,\$F9,MEDIAN(\$H9,\$F9,0))
I9:I14I9=(E9*D9)*100
J9:J14J9=(F9*D9)*100
K9:K14K9=J9-I9
L9:L14L9=IF(K9<>0,K9/I9,"")
M9:M14M9=IF(I9<>0,I9/\$I\$21,"")

FGHIJ
23Sold Options +/- %
24SectorGainStock %Sector %Count
25Elec Tech \$ (4,180.00)-43.18%107.12%1
26Tech Srvc \$ (18,640.00)-56.78%-54.53%4
27Retail \$ (1,650.00)-1.91%81.45%1
280 \$ -
29  \$ -
30  \$ -
Option Monster
Cell Formulas
RangeFormula
F25:F30F25=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(0, IF(COUNTIF(\$N\$9:\$N\$20,"Sold"),COUNTIF(\$F\$25:F25, \$B\$9:\$B\$20)), 0)),"")
G25:G30G25=IFERROR(SUMIFS(\$K\$9:\$K\$20,\$B\$9:\$B\$20,\$F25),"")
H25H25=IFERROR(AVERAGEIFS(\$L\$9:\$L\$20,\$B\$9:\$B\$20,\$F25),"")
I25:I30I25=IFERROR(VLOOKUP(\$F25,'Sector Perform-ALL'!\$G\$3:\$H\$24,2,0),"")
J25:J30J25=IF(\$F25=0,"",IF(\$F25="","",COUNTIF(B:B,\$F25)))
H26:H30H26=IFERROR(VLOOKUP(\$F26,\$B\$9:\$L\$20,11,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

#### gaz_chops

##### Well-known Member

I can't see how you are getting any results, you have a circular Ref!!

=IFERROR(INDEX(\$B\$9:\$B\$20, MATCH(0, IF(COUNTIF(\$N\$9:\$N\$20,"Sold"),COUNTIF(\$F\$25:F25, \$B\$9:\$B\$20)), 0)),"")

#### jasonb75

##### Well-known Member
Try this in F25 and fill down as needed. Does not need Ctrl Shift Enter.

=IFERROR(INDEX(\$B:\$B,AGGREGATE(15,6,ROW(\$B\$9:\$B\$20)/(\$N\$9:\$N\$20="Sold")/ISNA(MATCH(\$B\$9:\$B\$20,\$F\$24:\$F24,0)),1)),"")

#### gtd526

##### Active Member
Try this in F25 and fill down as needed. Does not need Ctrl Shift Enter.

=IFERROR(INDEX(\$B:\$B,AGGREGATE(15,6,ROW(\$B\$9:\$B\$20)/(\$N\$9:\$N\$20="Sold")/ISNA(MATCH(\$B\$9:\$B\$20,\$F\$24:\$F24,0)),1)),"")
WORKS! Thank you for the reply!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,682
Messages
5,838,787
Members
430,568
Latest member
bortey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back