Index, Match, CountIf with 1 criteria

gtd526

Active Member
Joined
Jul 30, 2013
Messages
297
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
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
297
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
297
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,468
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
11,980
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
297
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,864
Members
414,409
Latest member
FloordAlex

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