Index, Match, CountIf with 1 criteria

[gtd526]

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.

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.

 

[gaz_chops]

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]

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
	F	G	H	I	J
23	Sold Options +/- %
24	Sector	Gain	Stock %	Sector %	Count
25	Elec Tech	$ (4,180.00)	-43.18%	107.12%	1
26	Tech Srvc	$ (18,640.00)	-56.78%	-54.53%	4
27	Retail	$ (1,650.00)	-1.91%	81.45%	1
28	0	$ -
29		$ -
30		$ -
Option Monster

Cell Formulas
Range	Formula
F25:F30	F25	=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:G30	G25	=IFERROR(SUMIFS($K$9:$K$20,$B$9:$B$20,$F25),"")
H25	H25	=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$F25),"")
I25:I30	I25	=IFERROR(VLOOKUP($F25,'Sector Perform-ALL'!$G$3:$H$24,2,0),"")
J25:J30	J25	=IF($F25=0,"",IF($F25="","",COUNTIF(B:B,$F25)))
H26:H30	H26	=IFERROR(VLOOKUP($F26,$B$9:$L$20,11,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gtd526]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
7				OPTION MONSTER				OPTION MONSTER
8	Symbol	Sector	Type	Shares	Cost	CURRENT	MAX Value	MIN Value	Cost Value	Market Value	Gain/Loss	G/L %	Weight(%)	Expiration	Strike
9	RNG	Tech Srvc	Call	10	$ 15.27	$ 6.60	$ 31.20	$ 5.00	$ 15,270.00	$ 6,600.00	$ (8,670.00)	-56.78%	12%	7/31	270.00
10	NVDA	Elec Tech	Call	10	$ 9.68	$ 5.50	$ 16.05	$ 5.50	$ 9,680.00	$ 5,500.00	$ (4,180.00)	-43.18%	8%	7/31	417.50
11	DOCU	Tech Srvc	Call	5	$ 10.45	$ 3.45	$ 10.60	$ 3.45	$ 5,225.00	$ 1,725.00	$ (3,500.00)	-66.99%	4%	sold	202.50
12	ADSK	Tech Srvc	Call	10	$ 6.18	$ 3.10	$ 8.30	$ 3.10	$ 6,180.00	$ 3,100.00	$ (3,080.00)	-49.84%	5%	sold	245.00
13	WDAY	Tech Srvc	Call	10	$ 6.00	$ 2.61	$ 6.00	$ 2.61	$ 6,000.00	$ 2,610.00	$ (3,390.00)	-56.50%	5%	sold	192.50
14	AMZN	Retail	Call	5	$ 173.00	$ 169.70	$ 173.00	$ 167.65	$ 86,500.00	$ 84,850.00	$ (1,650.00)	-1.91%	67%	sold	2,995.00
Option Monster

Cell Formulas
Range	Formula
G9:G14	G9	=IF($F9=0,0,MAX($F9,$G9))
H9:H14	H9	=IF($H9=0,$F9,MEDIAN($H9,$F9,0))
I9:I14	I9	=(E9*D9)*100
J9:J14	J9	=(F9*D9)*100
K9:K14	K9	=J9-I9
L9:L14	L9	=IF(K9<>0,K9/I9,"")
M9:M14	M9	=IF(I9<>0,I9/$I$21,"")

The Whole Enchilada.xlsm
	F	G	H	I	J
23	Sold Options +/- %
24	Sector	Gain	Stock %	Sector %	Count
25	Elec Tech	$ (4,180.00)	-43.18%	107.12%	1
26	Tech Srvc	$ (18,640.00)	-56.78%	-54.53%	4
27	Retail	$ (1,650.00)	-1.91%	81.45%	1
28	0	$ -
29		$ -
30		$ -
Option Monster

Cell Formulas
Range	Formula
F25:F30	F25	=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:G30	G25	=IFERROR(SUMIFS($K$9:$K$20,$B$9:$B$20,$F25),"")
H25	H25	=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$F25),"")
I25:I30	I25	=IFERROR(VLOOKUP($F25,'Sector Perform-ALL'!$G$3:$H$24,2,0),"")
J25:J30	J25	=IF($F25=0,"",IF($F25="","",COUNTIF(B:B,$F25)))
H26:H30	H26	=IFERROR(VLOOKUP($F26,$B$9:$L$20,11,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gaz_chops]

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]

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]

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!