Retrieve cells with highest sumif values

[robert1012]

I have a list of stocks in column B and the paid amount in column C ("Pay").
I want excel to generate a list of the 5 stocks with the highest total "pay" amount based on a formula (preferably not a pivot table).

I have tried to use a combination of SUMIF & LARGE, but it's just not working.
In cells F2:G6 is the result I should obtain by using a formula.
Can anyone tell me if there's a formula I can use to get this result?

 

[KOKOSEK]

Could You paste this as sheet (use XL2bb addin, if You do not know how)?

 

[robert1012]

Could You paste this as sheet (use XL2bb addin, if You do not know how)?

Not sure how this works, I tried to follow the steps in the description by copying the "Mini Sheet" from excel:

Generate Largest Sumif values.xlsx
	G
12
Sheet1

 

[robert1012]

Generate Largest Sumif values.xlsx
	A	B	C	D	E	F	G
1	#	Stock	Pay		Top	Stock	Pay
2	1	BAC	$ 100.0		1	BAC	$ 1,099.4
3	2	GLD	$ 56.7		2	WFC	$ 272.0
4	3	WFC	$ 56.3		3	FB	$ 225.1
5	4	WU	$ 43.7		4	MA	$ 225.1
6	5	VXX	$ 59.2		5	JPM	$ 197.5
7	6	CROX	$ 47.7
8	7	XLF	$ 44.8		Top	Stock	Pay
9	8	Book	$ 49.0		1
10	9	C	$ 58.0		2
11	10	GS	$ 43.6		3
12	11	Book	$ 53.8		4
13	12	BRK	$ 75.7		5
14	13	JPM	$ 52.9
15	14	FB	$ 75.2
16	15	BAC	$ 68.2
17	16	GLD	$ 65.6
18	17	ING	$ 47.9
19	18	DB	$ 54.1
20	19	XXX	$ 39.7
21	20	BTC	$ 38.7
22	21	GOOGL	$ 88.2
23	22	LUK	$ 75.9
24	23	WFC	$ 80.5
25	24	BAC	$ 84.2
26	25	BAC	$ 57.4
27	26	BRK	$ 117.6
28	27	GOOGL	$ 75.4
29	28	FB	$ 79.9
30	29	BAC	$ 107.7
31	30	AAPL	$ 83.7
32	31	FB	$ 70.0
33	32	C	$ 71.9
34	33	BAC	$ 63.8
35	34	AAPL	$ 80.2
36	35	BAC	$ 65.6
37	36	V	$ 55.7
38	37	MA	$ 55.0
39	38	BAC	$ 54.5
40	39	AXP	$ 54.9
41	40	MA	$ 56.4
42	41	MA	$ 53.7
43	42	BAC	$ 95.5
44	43	BUD	$ 70.7
45	44	SPY	$ 98.9
46	45	BAC	$ 126.0
47	46	BTC	$ 59.3
48	47	SPY	$ 47.8
49	48	QQQ	$ 54.8
50	49	V	$ 95.1
51	50	BAC	$ 113.7
52	51	MA	$ 60.1
53	52	WFC	$ 135.3
54	53	JPM	$ 82.1
55	54	BAC	$ 77.6
56	55	JPM	$ 62.5
57	56	BAC	$ 85.2
58	57	SPY	$ 35.1
59	58	UMIC	$ 84.0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:H6	Cell Value	=""	text	NO
G2:H6	Expression	=$F2<4	text	NO
G2:H6	Expression	=AND($F2<10,$F2>3)	text	NO

 

[Sergius]

Hi! This should work.

Book1.xlsm
	A	B	C	D	E	F	G
1	#	Stock	Pay		Top	Stock	Pay
2	1	BAC	100,03		1	WFC	135,26
3	2	GLD	56,67		2	BAC	126,00
4	3	WFC	56,28		3	BRK	117,55
5	4	WU	43,70		4	BAC	113,74
6	5	VXX	59,20		5	BAC	107,74
7	6	CROX	47,67
8	7	XLF	44,80
9	8	Book	48,95
10	9	C	57,96
11	10	GS	43,62
12	11	Book	53,80
13	12	BRK	75,74
14	13	JPM	52,91
15	14	FB	75,22
16	15	BAC	68,22
17	16	GLD	65,56
18	17	ING	47,93
19	18	DB	54,06
20	19	XXX	39,74
21	20	BTC	38,73
22	21	GOOGL	88,23
23	22	LUK	75,86
24	23	WFC	80,46
25	24	BAC	84,18
26	25	BAC	57,41
27	26	BRK	117,55
28	27	GOOGL	75,39
29	28	FB	79,92
30	29	BAC	107,74
31	30	AAPL	83,68
32	31	FB	69,98
33	32	C	71,93
34	33	BAC	63,80
35	34	AAPL	80,16
36	35	BAC	65,59
37	36	V	55,67
38	37	MA	54,96
39	38	BAC	54,46
40	39	AXP	54,89
41	40	MA	56,36
42	41	MA	53,65
43	42	BAC	95,50
44	43	BUD	70,72
45	44	SPY	98,88
46	45	BAC	126,00
47	46	BTC	59,34
48	47	SPY	47,79
49	48	QQQ	54,80
50	49	V	95,08
51	50	BAC	113,74
52	51	MA	60,14
53	52	WFC	135,26
54	53	JPM	82,08
55	54	BAC	77,55
56	55	JPM	62,50
57	56	BAC	85,18
58	57	SPY	35,11
59	58	UMIC	84,01
Sheet7

Cell Formulas
Range	Formula
F2:F6	F2	=INDEX($B$2:$B$59,MATCH(LARGE($C$2:$C$59,$E2),$C$2:$C$59,0))
G2:G6	G2	=LARGE($C$2:$C$59,$E2)

 

[KOKOSEK]

Hmm for XL365 is possible to sort with 1 formula. For 2019 I've used 3 helpers columns (data as on original in columns A1:C59):

Book1
	E	F	G	H	I	J	K
7	XL2019				XL365
8	Top	Stock	Pay		Top	Stock	Pay
9	1	BAC	1099.4		1	BAC	1099.4
10	2	WFC	272		2	WFC	272
11	3	FB	225.12		3	FB	225.12
12	4	MA	225.11		4	MA	225.11
13	5	JPM	197.49		5	JPM	197.49
14
15		helpers for 2019
16		BAC	1099.4	1
17		GLD	122.23	12
18		WFC	272	2
19		WU	43.7	25
20		VXX	59.2	18
21		CROX	47.67	23
22		XLF	44.8	24
23		Book	102.75	13
24		C	129.89	11
25		GS	43.62	26
26		BRK	193.2942	6
27		JPM	197.49	5
28		FB	225.12	3
29		ING	47.93	22
30		DB	54.06	21
31		XXX	39.74	27
32		BTC	98.07	14
33		GOOGL	163.62	9
34		LUK	75.86	16
35		AAPL	163.84	8
36		V	150.75	10
37		MA	225.11	4
38		AXP	54.89	19
39		BUD	70.72	17
40		SPY	181.78	7
41		QQQ	54.8	20
42		UMIC	84.01	15
Sheet1

Cell Formulas
Range	Formula
F9	F9	=INDEX($F$16:$F$42,MATCH($E9,$H$16:$H$42,0))
G9:G13	G9	=INDEX($G$16:$G$42,MATCH($E9,$H$16:$H$42,0))
F10:F13	F10	=INDEX($F$16:$F$42,MATCH(E10,$H$16:$H$42,0))
J9:K13	J9	=LET(xNames,UNIQUE($B$2:$B$59),xValues,SUMIFS($C$2:$C$59,$B$2:$B$59,xNames),xResult,HSTACK(xNames,xValues),CHOOSEROWS(SORT(xResult,2,-1),ROW(1:5)))
F16:F42	F16	=IFERROR(INDEX($B$2:$B$59,MATCH(0,COUNTIF($F$15:F15,$B$2:$B$59),0)),"")
G16:G42	G16	=SUMIFS($C$2:$C$59,$B$2:$B$59,$F16)
H16:H42	H16	=RANK($G16,$G$16:$G$42)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.