Summarize Unique Values from High to Low

[kumara_faith]

Hi,

I have the following Table 1:

Book1
	D	E
2	Table 1
3	Sector	%
4	Technology	15%
5	Technology	11%
6	Technology	14%
7	Consumer	5%
8	Technology	5%
9	Index	4%
10	Technology	11%
11	Healthcare	3%
12	Technology	4%
13	Financial	4%
14	Technology	9%
15	Technology	3%
16	Index	5%
17	Financial	1%
18	Technology	3%
19	Consumer	2%
20	Crypto	1%
21	Technology	1%
Sheet1

In Table 2 below, I am trying to summarize the data from Table 1 with unique values sorted automatically from high to low:

Book1
	H	I
2	Table 2
3	Sector	%
4	Technology	15%
5	Technology	14%
6	Technology	11%
7	Technology	11%
8	Technology	9%
9	Consumer	5%
10	Index	5%
11	Technology	5%
12	Technology	4%
13	Index	4%
14	Financial	4%
15	Technology	3%
16	Healthcare	3%
17	Technology	3%
18	Consumer	2%
19	Crypto	1%
20	Technology	1%
21	Financial	1%
Sheet1

Cell Formulas
Range	Formula
H4:H21	H4	=IF(I4<>"",INDEX($D$4:$D$21,SMALL(IF($E$4:$E$21=I4,ROW($E$4:$E$21)-ROW($E$4)+1),COUNTIF($I4:I$21,I4))),"")
I4:I21	I4	=LARGE($E$4:$E$21,ROWS($A$3:$A3))

The formula does not seem to work properly. The correct result should be as below:

Book1
	K	L
4	Sector	%
5	Technology	76%
6	Index	9%
7	Consumer	6%
8	Financial	5%
9	Healthcare	3%
10	Crypto	1%
Sheet1

Appreciate any help.

 

[DanteAmor]

You can use a pivot table and order by the sum of the %:

 

[kumara_faith]

Hi Dante,

Thank you for your response. However, I would like to use a formula solution. Is there a way to amend the current formula to achieve this ?

 

[Fluff]

How about

+Fluff 1.xlsm
	D	E	F	G	H	I
1
2	Table 1
3	Sector	%			Technology	76%
4	Technology	15%			Index	9%
5	Technology	11%			Consumer	6%
6	Technology	14%			Financial	5%
7	Consumer	5%			Healthcare	3%
8	Technology	5%			Crypto	1%
9	Index	4%
10	Technology	11%
11	Healthcare	3%
12	Technology	4%
13	Financial	4%
14	Technology	9%
15	Technology	3%
16	Index	5%
17	Financial	1%
18	Technology	3%
19	Consumer	2%
20	Crypto	1%
21	Technology	1%
22
Data

Cell Formulas
Range	Formula
H3:I8	H3	=LET(u,UNIQUE(D4:D21),SORT(CHOOSE({1,2},u, SUMIFS(E4:E21,D4:D21,u)),2,-1))
Dynamic array formulas.

 

[kumara_faith]

Hi Fluff,

That worked. Thank you and appreciate your valuable time and patience. Have a great day.?

 

[Fluff]

Glad we could help & thanks for the feedback.