Sum the 10 largest values for ID

[jevi]

Hi All,

I have different columns and 150.000 rows, in column F I have the client ID, while in column BH I have the amount. I would like to have a formula (not a pivot table as I did it with Pivot) to sum only one amount but it should be included the amount per ID with the 10 nth largest amounts.

Example:

ID 10 it might be reapeated a lot of times in row F, so I need the sum of ID10 (all the amounts).

So i need the 10 largest amounts per ID just a sum of them all.

Thank you,

 

[Fluff]

Can you post some sample data along with the expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jevi]

Hi Pluff,

Sorry but I can't download as is my work PC. But i did an immagine so hopefully it can help.
So my result would be the column E, but it seemed I can't do it for ID like column C and D....honestly I don't need the column C and D detail but still would be nice to have it as they are only the first 10 nth ID with largest amount.
Thank you,

 

[Fluff]

Ok, ow about

++Fluff.xlsm
	A	B	C	D	E
1
2	101	20	101	1720	4520
3	102	10	102	210
4	101	700	126	200
5	126	200	130	400
6	101	1000	22	30
7	102	200	14	0
8	130	400	18	10
9	22	30	300	0
10	14	0	200	500
11	18	10	4	1400
12	300	0	5	20
13	200	500	6	30
14	4	1400	7	1
15	5	20	8	5
16	6	30
17	7	1
18	8	5
19
Main

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(D2:D20>=LARGE(D2:D20,10)),D2:D20)
C2:C17	C2	=IFNA(INDEX($A$2:$A$18,MATCH(0,COUNTIFS(C$1:C1,$A$2:$A$18),0)),"")
D2:D18	D2	=IF(C2="","",SUMIFS(B:B,A:A,C2))

The formula in C2 may need array entry for your version

 

[jevi]

Ok, ow about

++Fluff.xlsm
	A	B	C	D	E
1
2	101	20	101	1720	4520
3	102	10	102	210
4	101	700	126	200
5	126	200	130	400
6	101	1000	22	30
7	102	200	14	0
8	130	400	18	10
9	22	30	300	0
10	14	0	200	500
11	18	10	4	1400
12	300	0	5	20
13	200	500	6	30
14	4	1400	7	1
15	5	20	8	5
16	6	30
17	7	1
18	8	5
19
Main

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(D2:D20>=LARGE(D2:D20,10)),D2:D20)
C2:C17	C2	=IFNA(INDEX($A$2:$A$18,MATCH(0,COUNTIFS(C$1:C1,$A$2:$A$18),0)),"")
D2:D18	D2	=IF(C2="","",SUMIFS(B:B,A:A,C2))

The formula in C2 may need array entry for your version

Hi Pluff,

Thank you for the formulas and they work great but the formula in column C i have 150.000 rows so it is heavy for the file. I was looking for something to give me the result of column E with the database of column A and B....so just have the result. Maybe is not possible so I will use the Pivot as I already did them but was hoping for a formula just to have the amount of the 10nth biggest import from the list.

Thank you,

 

[Fluff]

I Know of no way to just get the final result in one formula for your version of Excel.

 

[jevi]

Hi Pluff,

I am sure you know better. I marked it as a solution as in a way it gives the result....maybe a Macro can do it faster than Pivot Tables and Formulas?

Thank you

 

[Fluff]

I have thought of a way of doing it in one formula, but suspect that a pivot would be better.

++Fluff.xlsm
	A	B	C	D
1	County	Qty
2	Surrey	96		1364
3	Greater London	56
4	Somerset	35
5	Bedfordshire	65
6	Greater London	94
7	Tyne and Wear	41
8	East Sussex	7
9	Hertfordshire	54
10	Cambridgeshire	78
11	Gloucestershire	55
12	Tyne and Wear	92
13	Lancashire	6
14	Hampshire	82
15	Greater London	16
16	Greater London	30
17	West Yorkshire	67
18	Hampshire	35
19	Greater London	42
20	Hertfordshire	80
21	Staffordshire	7
22	Tyne and Wear	26
23	Nottinghamshire	80
24	Wiltshire	85
25	Staffordshire	96
26	Derbyshire	57
27	Leicestershire	42
28	County Durham	84
29	Greater London	25
30	West Sussex	77
31	Greater London	20
32	Lincolnshire	99
33	Berkshire	42
34	Leicestershire	1
35	South Yorkshire	2
36	Surrey	65
37	Leicestershire	55
38	West Midlands	17
39	Nottinghamshire	45
40	Northamptonshire	5
Main

Cell Formulas
Range	Formula
D2	D2	=SUM(LARGE(MMULT((A2:A40=TRANSPOSE(A2:A40))*(MMULT((ROW(1:39)>=TRANSPOSE(ROW(1:39)))*(A2:A40=TRANSPOSE(A2:A40)),ROW(1:39)^0)=1),B2:B40),ROW(1:10)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jevi]

I have thought of a way of doing it in one formula, but suspect that a pivot would be better.

++Fluff.xlsm
	A	B	C	D
1	County	Qty
2	Surrey	96		1364
3	Greater London	56
4	Somerset	35
5	Bedfordshire	65
6	Greater London	94
7	Tyne and Wear	41
8	East Sussex	7
9	Hertfordshire	54
10	Cambridgeshire	78
11	Gloucestershire	55
12	Tyne and Wear	92
13	Lancashire	6
14	Hampshire	82
15	Greater London	16
16	Greater London	30
17	West Yorkshire	67
18	Hampshire	35
19	Greater London	42
20	Hertfordshire	80
21	Staffordshire	7
22	Tyne and Wear	26
23	Nottinghamshire	80
24	Wiltshire	85
25	Staffordshire	96
26	Derbyshire	57
27	Leicestershire	42
28	County Durham	84
29	Greater London	25
30	West Sussex	77
31	Greater London	20
32	Lincolnshire	99
33	Berkshire	42
34	Leicestershire	1
35	South Yorkshire	2
36	Surrey	65
37	Leicestershire	55
38	West Midlands	17
39	Nottinghamshire	45
40	Northamptonshire	5
Main

Cell Formulas
Range	Formula
D2	D2	=SUM(LARGE(MMULT((A2:A40=TRANSPOSE(A2:A40))*(MMULT((ROW(1:39)>=TRANSPOSE(ROW(1:39)))*(A2:A40=TRANSPOSE(A2:A40)),ROW(1:39)^0)=1),B2:B40),ROW(1:10)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you so much Pluff....but really still Pivot is good I just was curios doing with a formula. Is not working at my file because I guess that they are rows that have zero in column B so that might be a reason...but no problem at all. Still great formula, you are such a genious