Group "all others" from a list

[Stephaneky]

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

 

[cyrilbrd]

Hi,

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

 

[Stephaneky]

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.

 

[cyrilbrd]

Ok,
if:

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

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?

 

[Stephaneky]

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.

 

[cyrilbrd]

Ok, let us know if it works!

Cheers.