# Sorting List and Grouping Totals Help

#### Dellboy

##### New Member
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
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
+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
+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
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

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
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.

#### Dellboy

##### New Member
Thanks Fluff, working. Appreciate the help

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
10
Views
304
Replies
0
Views
48
Replies
0
Views
134
Replies
2
Views
49
Replies
0
Views
166

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

### 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