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

johannaexcel

New Member
Joined
Mar 23, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.

1616493598828.png
 

Excel Facts

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEFGH
1Dallas
2MarkTexas200Mark300
3PeterTexas20Cindy140
4CindyDallas40Lisa90
5JoannaNew York25Jennifer20
6PeterNew York133Peter0
7JohnNew York60Joanna0
8JohnNew York38John0
9CindyDallas100
10MarkDallas300
11JenniferDallas20
12LisaDallas90
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
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
Forgot the top 10 requirement.

+Fluff 1.xlsm
ABCDEFGH
1Dallas
2MarkTexas200Mark600
3PeterTexas20Mary300
4CindyDallas40Cindy180
5JoannaNew York25Lisa180
6PeterNew York133Dave100
7JohnNew York60Lorna100
8JohnNew York38Ron90
9CindyDallas100Jennifer40
10MarkDallas300Paul40
11JenniferDallas20Sue20
12LisaDallas90
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
31LornaDallas100
32MarkDallas300
33JenniferDallas20
34LisaDallas90
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top