Index, Match, CountIf with 1 criteria

gtd526

Active Member
Joined
Jul 30, 2013
Messages
477
Office Version
  1. 2019
Platform
  1. 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.

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
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
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)),"")

If all, then add =12

=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
Joined
Jul 30, 2013
Messages
477
Office Version
  1. 2019
Platform
  1. Windows
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)),"")

If all, then add =12

=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.

The Whole Enchilada.xlsm
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
Joined
Jul 30, 2013
Messages
477
Office Version
  1. 2019
Platform
  1. Windows
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)),"")

If all, then add =12

=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.

The Whole Enchilada.xlsm
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,"")



The Whole Enchilada.xlsm
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
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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)),"")
 
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
477
Office Version
  1. 2019
Platform
  1. Windows
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!
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top