Group "all others" from a list

Stephaneky

New Member
Joined
Jun 4, 2012
Messages
3
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Ok, let us know if it works!

Cheers.
;)
 
Upvote 0

Forum statistics

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

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