kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 922
- Office Version
- 365
Hi,
I have the following Table 1:
In Table 2 below, I am trying to summarize the data from Table 1 with unique values sorted automatically from high to low:
The formula does not seem to work properly. The correct result should be as below:
Appreciate any help.
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.