# Top summed value list with criteria (INDEX / SORT / SEQUENCE function combination)

#### johannaexcel

##### New Member
I am trying to create a top 10 summed value list with a criteria (in cell G1), but my formula is not working. I used this formula:

=INDEX(SORT(CHOOSE({1;2};UNIQUE(FILTER(A2:A12;B2:B12=H1));SUMIFS(C2:C12;B2:B12;H1;A2:A12;UNIQUE(FILTER(A2:A12;B2:B12=H1))));2;-1);SEQUENCE(10;2);{1;2})

I would like to be able to come up with the Top 10 Sales person after adding up the values for each sales in Dallas. Can you help me how to create a summed value list? Thank you.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+Fluff 1.xlsm
ABCDEFGH
1Dallas
2MarkTexas200Mark300
3PeterTexas20Cindy140
4CindyDallas40Lisa90
5JoannaNew York25Jennifer20
6PeterNew York133Peter0
7JohnNew York60Joanna0
8JohnNew York38John0
9CindyDallas100
10MarkDallas300
11JenniferDallas20
13
Main
Cell Formulas
RangeFormula
F2:G8F2=LET(u,UNIQUE(A2:A12),s,SUMIFS(C2:C12,B2:B12,H1,A2:A12,u),SORT(INDEX(CHOOSE({1,2},u,s),),2,-1))
Dynamic array formulas.

#### Fluff

##### MrExcel MVP, Moderator
Forgot the top 10 requirement.

+Fluff 1.xlsm
ABCDEFGH
1Dallas
2MarkTexas200Mark600
3PeterTexas20Mary300
4CindyDallas40Cindy180
5JoannaNew York25Lisa180
6PeterNew York133Dave100
7JohnNew York60Lorna100
8JohnNew York38Ron90
9CindyDallas100Jennifer40
10MarkDallas300Paul40
11JenniferDallas20Sue20
13MarkTexas200
14PeterTexas20
15PaulDallas40
16JoannaNew York25
17PeterNew York133
18JohnNew York60
19JohnNew York38
20DaveDallas100
21MaryDallas300
22SueDallas20
23RonDallas90
24MarkTexas200
25PeterTexas20
26CindyDallas40
27JoannaNew York25
28PeterNew York133
29JohnNew York60
30JohnNew York38
32MarkDallas300
33JenniferDallas20
Main
Cell Formulas
RangeFormula
F2:G11F2=LET(u,UNIQUE(A2:A34),s,SUMIFS(C:C,B:B,H1,A:A,u),st,SORT(INDEX(CHOOSE({1,2},u,s),),2,-1),INDEX(st,SEQUENCE(MIN(ROWS(u),10)),{1,2}))
Dynamic array formulas.

Replies
1
Views
96
Replies
4
Views
95
Replies
7
Views
283
Replies
4
Views
234
Replies
6
Views
84

1,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back