SORTBY Combined with UNIQUEFILTER

[Citrusandsage]

I need help so I have 2 columns I’m working off of, column B is a unique filter of industry being pulled from a control tab and then Column C is the number of opportunities. I want the Unique filter in column B to automatically sort by the number of opportunities high to low. The UNIQUEFILTER formula is
=UNIQUE(FILTER(Control!$N$4:$N$80,(Control!$N$4:$N$80<>0)))

how the heck do I get this to work? The range in which I need to sort is C7:C44

 

[Eric W]

Try:

Book1
	A	B	C	D	E									N	O
1
2
3														Industry	Opportunities
4														Pharm	4
5														Tech	2
6		Industry	Opportunities											Pharm	3
7		Pharm	16											Manufacturing	2
8		Service	13											Pharm	8
9		Tech	4											Tech	2
10		Manufacturing	2											Service	6
11														Service	7
12														Pharm	1
13
Control

Cell Formulas
Range	Formula
B7:C10	B7	=SORT(UNIQUE(CHOOSE({1,2},FILTER(Control!N4:N80,Control!N4:N80<>""),SUMIF(Control!N4:N80,FILTER(Control!N4:N80,Control!N4:N80<>""),Control!O4:O80))),2,-1)
Dynamic array formulas.

 

[ISY]

Hi

=SORT(CHOOSE({1,2},UNIQUE(N4:N12),SUMIF(N4:N12,UNIQUE(N4:N12),O4:O12)),2,-1)

 

[Eric W]

ISY, I started out with that version, but it requires that you put in the actual ranges (N4:N12, etc.). If you put in the maximum range (N4:N80) and there are blank cells, that will result in a 0 value for the last row. Which is why I switched to the more awkward form I posted.

 

[ISY]

Hi

Alternative
=SORT(CHOOSE({1,2},FILTER(UNIQUE(N4:N80),UNIQUE(N4:N80)>0),SUMIF(N4:N80,FILTER(UNIQUE(N4:N80),UNIQUE(N4:N80)>0),O4:O80)),2,-1)