Sorting List and Grouping Totals Help

Dellboy

New Member
Joined
Sep 16, 2020
Messages
33
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: 13

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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"?
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top