Change array to formula

[gtd526]

Hello,
Can this array be changed to a formula?
Column A: ={IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")}
Thank you!

Options Monster.xlsm
	A	B	C	D
22	Total +/- per Sector
23	Sector	Gain	Stock %	Sector %
24	Prod Man	$ (3,200.00)	-96.97%
25	Tech Srvc	$ 11,325.00	46.30%
26	Retail	$ 123,450.00	329.60%
27	Elec Tech	$ 15,550.00	126.18%	71.03%
28		$ -
29		$ -
30		$ -
31		$ -
32		$ -
33	Total:	$ 147,125.00	101.28%	71.03%
Options Monster

Cell Formulas
Range	Formula
A24:A32	A24	=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")
B24:B32	B24	=SUM(SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
C24:C32	C24	=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A$24:$A$32),"")
B33	B33	=SUM(B24:B32)
C33	C33	=IFERROR(AVERAGE(C$24:C$32),"")
D33	D33	=AVERAGE(D$24:D$32)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

That is a formula
What version of Excel are you using?
Please change your account details to show this, as it affects what functions you can use.

 

[gtd526]

That is a formula
What version of Excel are you using?
Please change your account details to show this, as it affects what functions you can use.

2019 Office
Its entered as an Array. My excel for this worksheet is very slow. Im just assuming it the Array formula? No other worksheets are this slow.

 

[Fluff]

An array formula for that small a range shouldn't be any problem, but a non-array version is
=IFERROR(INDEX($B$9:$B$20, AGGREGATE(15,6,(ROW($B$9:$B$20)-ROW($B$9)+1)/(ISNA(MATCH($B$9:$B$20,A$23:A23,0))),ROWS(A$24:A24))),"")

You formula in B can be done like
=SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
no need for the sum function

also for column C if you are trying to get the average of col L if B9:B20 is A24 use
=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A24),"")
filled down

 

[gtd526]

2019 Office
So I can remove the brackets, to act a formula?

Options Monster.xlsm
	A	B	C	D
22	Total +/- per Sector
23	Sector	Gain	Stock %	Sector %
24	Prod Man	$ (3,200.00)	-96.97%
25	Tech Srvc	$ 11,325.00	46.30%
26	Retail	$ 123,450.00	329.60%
27	Elec Tech	$ 15,550.00	126.18%	71.03%
28		$ -
29		$ -
30		$ -
31		$ -
32		$ -
33	Total:	$ 147,125.00	101.28%	71.03%
Options Monster

Cell Formulas
Range	Formula
A24:A32	A24	=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")
B24:B32	B24	=SUM(SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
C24:C32	C24	=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A$24:$A$32),"")
B33	B33	=SUM(B24:B32)
C33	C33	=IFERROR(AVERAGE(C$24:C$32),"")
D33	D33	=AVERAGE(D$24:D$32)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B33:D33	Cell Value	<0	text	NO
B33:D33	Cell Value	>0	text	NO

An array formula for that small a range shouldn't be any problem, but a non-array version is
=IFERROR(INDEX($B$9:$B$20, AGGREGATE(15,6,(ROW($B$9:$B$20)-ROW($B$9)+1)/(ISNA(MATCH($B$9:$B$20,A$23:A23,0))),ROWS(A$24:A24))),"")

You formula in B can be done like
=SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
no need for the sum function

also for column C if you are trying to get the average of col L if B9:B20 is A24 use
=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A24),"")
filled down

I've made the changes you mentioned. Works fine. Thank you.
I will restart Excel and see if the speed has risen.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[gtd526]

Options Monster.xlsm
	A	B	C	D
22	Total +/- per Sector
23	Sector	Gain	Stock %	Sector %
24	Prod Man	$ (3,200.00)	-96.97%
25	Tech Srvc	$ 11,325.00	46.30%
26	Retail	$ 123,450.00	329.60%
27	Elec Tech	$ 15,550.00	126.18%	71.03%
28		$ -
29		$ -
30		$ -
31		$ -
32		$ -
33	Total:	$ 147,125.00	101.28%	71.03%
Options Monster

Cell Formulas
Range	Formula
A24:A32	A24	=IFERROR(INDEX($B$9:$B$20, MATCH(0, COUNTIF($A$24:A24, $B$9:$B$20), 0)),"")
B24:B32	B24	=SUM(SUMIFS($K$9:$K$20,$B$9:$B$20,A24))
C24:C32	C24	=IFERROR(AVERAGEIFS($L$9:$L$20,$B$9:$B$20,$A$24:$A$32),"")
B33	B33	=SUM(B24:B32)
C33	C33	=IFERROR(AVERAGE(C$24:C$32),"")
D33	D33	=AVERAGE(D$24:D$32)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B33:D33	Cell Value	<0	text	NO
B33:D33	Cell Value	>0	text	NO

I've made the changes you mentioned. Works fine. Thank you.
I will restart Excel and see if the speed has risen.

I did a copy/paste to new worksheet, and its performing normally, not like before.
Maybe it didn't perform correctly because I had links to other worksheets that were either renamed or deleted?
It might have been searching for those worksheets and couldn't find them, continuously looping, causing the delay??
Now I'm a Happy Camper!! thx for your input.