# Group "all others" from a list

#### Stephaneky

##### New Member
I am working with a spreadsheet that lists the transactions of over 100 payers for several years. I primarily care about the top 15 payers which I can figure out easily but I need to group all the remaining payers in a category called "all others". I then need to use SumIfs to calculate payment amounts by transaction date.
Since the top 15 are static, I can use the individual name of the payer in the SumIfs formula, but how do I group the "all others" without naming them all individually?

StephanekY

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Hi,

would you consider adding one helper column? all others could then be named similarly.

Hi,

would you consider adding one helper column? all others could then be named similarly.
Thank you cyrilbrd,

That was one of my thoughts. The other which I have used, was to use "<>abc" and "<>bcd" etc... basically excluding the top 15 that way but I didn't find my solution to be particularly elegant.

Ok,
if:
HTML:
``````names	this	that	sum
t1	664	1259	1923
t2	1280	1268	2548
t3	168	1983	2151
t4	933	1272	2205
t5	1290	603	1893
t6	1586	1580	3166
t7	1704	1162	2866
t8	567	619	1186
t9	573	152	725
t10	1575	1759	3334
t11	724	1926	2650
t12	549	388	937``````

In E1 type "group"
in E2 type:
=IF(SUM(1*(D2<\$D\$2:\$D\$101))+1+IF(ROW(D2)-ROW(\$D\$2)=0,0,
SUM(1*(D2=OFFSET(\$D\$2,0,0,
INDEX(ROW(D2)-ROW(\$D\$2)+1,1)-1,1))))<=15,A2,"others")
Ctrl + Shift + Enter, not just enter copied down till needed.
This would give you for the top 15 (highest) the names from Column A and for the lower 85 the word "other".

HTML:
``````names	this	that	sum	Group
t1	664	1259	1923	others
t2	1280	1268	2548	others
t3	168	1983	2151	others
t4	933	1272	2205	others
t5	1290	603	1893	others
t6	1586	1580	3166	t6
t7	1704	1162	2866	t7
t8	567	619	1186	others
t9	573	152	725	others
t10	1575	1759	3334	t10
t11	724	1926	2650	others
t12	549	388	937	others``````

Would that work for you?

Last edited:
Now this is much more elegant!

Thank you Cyrilbrd....

I will give it a try with my application, but from the looks of it, it is exactly what I needed.

Ok, let us know if it works!

Cheers.

Replies
17
Views
468
Replies
1
Views
581
Replies
10
Views
951
Replies
1
Views
351
Replies
3
Views
167

1,211,983
Messages
6,105,215
Members
447,956
Latest member
haluxuxjr

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

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