Sorting List and Grouping Totals Help

Dellboy

New Member
Joined
Sep 16, 2020
Messages
23
Office Version
  1. 365
Afternoon All,

Hope someone can help me with this.

I'm looking to develop a formula that sorts the values of a column and returns the corresponding rows. See attachment.

I can easily sort a top 10 list which sorts the value and returns the Group and Type using this;
=INDEX(SORT($B$2:$D$11,3,-1,),ROW(1:10),{1,2,3})

However, how can I make it that I can match the groups and show the average age? e.g. Only show one line for Group C and the average age for all 3?

So I would essentially want a top 3 list for the Groups, as highlighted in yellow on the attachment.
 

Attachments

  • Capture.JPG
    Capture.JPG
    68.1 KB · Views: 6

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFG
1
2A50
3A75C200
4A100B187.5
5B200A75
6B200
7B250
8B100
9C300
10C100
11C200
12
Result
Cell Formulas
RangeFormula
F3:G5F3=LET(Uni,UNIQUE(B2:B11),Avg,AVERAGEIFS(D2:D11,B2:B11,Uni),SORT(CHOOSE({1,2},Uni,Avg),2,-1))
Dynamic array formulas.


Although I don't see how you get an average of 217 for B
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
23
Office Version
  1. 365
How about
+Fluff 1.xlsm
ABCDEFG
1
2A50
3A75C200
4A100B187.5
5B200A75
6B200
7B250
8B100
9C300
10C100
11C200
12
Result
Cell Formulas
RangeFormula
F3:G5F3=LET(Uni,UNIQUE(B2:B11),Avg,AVERAGEIFS(D2:D11,B2:B11,Uni),SORT(CHOOSE({1,2},Uni,Avg),2,-1))
Dynamic array formulas.


Although I don't see how you get an average of 217 for B
That was a mistake, apologies!
I've tried that but get #Name error, im using O365 excel if that makes a difference?
Thanks for help so far
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You may not have the LET function yet, try
Excel Formula:
=SORT(CHOOSE({1,2},UNIQUE(B2:B11),AVERAGEIFS(D2:D11,B2:B11,UNIQUE(B2:B11))),2,-1)
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
23
Office Version
  1. 365

ADVERTISEMENT

Yes, thats worked, once again, thanks Fluff, always helpful.

One final thing, how would I sort it if I only wanted to count the Open items? I assume I would add a countif somewhere, select C2:C11 and type "Open"?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
How about, sorted by count high to low
+Fluff 1.xlsm
ABCDEFGH
1
2AOpen50
3AOpen75A752
4AClosed100B187.52
5BClosed200C2001
6BOpen200
7BClosed250
8BOpen100
9COpen300
10CClosed100
11CClosed200
12
Result
Cell Formulas
RangeFormula
F3:H5F3=SORT(CHOOSE({1,2,3},UNIQUE(B2:B11),AVERAGEIFS(D2:D11,B2:B11,UNIQUE(B2:B11)),COUNTIFS(C2:C11,"Open",B2:B11,UNIQUE(B2:B11))),3,-1)
Dynamic array formulas.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

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