Book1 |
---|
|
---|
| B | C | D | E | F |
---|
1 | state | value | | N | 5 |
---|
2 | FL | 200 | | state count | 7 |
---|
3 | TX | 200 | | Nth Value | 200 |
---|
4 | FL | 100 | | adjusted N | 5 |
---|
5 | FL | 100 | | state | total value |
---|
6 | MN | 400 | | MN | 700 |
---|
7 | MA | 100 | | CO | 500 |
---|
8 | TX | 200 | | FL | 400 |
---|
9 | CA | 100 | | TX | 400 |
---|
10 | CA | 100 | | CA | 200 |
---|
11 | CO | 500 | | | |
---|
12 | MN | 300 | | | |
---|
13 | NY | 100 | | | |
---|
|
---|
F1: 5 (desired Top N)
In F2 control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),1))
In F3 control+shift+enter, not just enter:
=LARGE(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13)),MIN(F1,F2))
In F4 control+shift+enter, not just enter:
=SUM(IF(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13))>=F3,1))
This adjusts N for ties if applicable.
In E6 control+shift+enter, not just enter, and copy down:
=IF($F6="","",INDEX($B$2:$B$13,SMALL(IF(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13))=$F6,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIFS($F$6:F6,F6))))
In F6 control+shift+enter, not just enter, and copy down:
=IF(ROWS($F$6:F6)>$F$4,"",LARGE(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13)),ROWS($F$6:F6)))