Count top 10 per frequency during the year

[Minx_Minxy]

Hello,

I really have no idea where to start and not even sure if this is available in excel.

I have a count of payments per branch, this is split into months. Now, I want to identify the top 10 branches with the highest count but also with the most frequency. For example, I know that Branches 2, 4, 5 and 9 should not be in the top 10 because the payments are not occurring as often.


Appreciate the input in advance

Regards,

Query.xlsx
	A	B	C	D	E	F	G	H
2	COUNT Per Month						Top 10 Count
3	Branch Name	Month	COUNTA of Branch Name				Branch Name	Count
4	Branch 1	01 Jan	2
5		02 Feb	2
6		03 Mar	10
7		04 Apr	5
8		05 May	2
9		06 Jun	6
10		07 Jul	8
11		08 Aug	1
12		09 Sep	6
13		10 Oct	6
14		11 Nov	15
15		12 Dec	4
16	Branch 1 Total		67
17	Branch 2	01 Jan	35
18		05 May	6
19		07 Jul	6
20		09 Sep	2
21	Branch 2 Total		49
22	Branch 3	05 May	1
23		06 Jun	1
24		08 Aug	8
25		09 Sep	4
26		10 Oct	6
27		11 Nov	17
28		12 Dec	4
29	Branch 3 Total		41
30	Branch 4	03 Mar	36
31		09 Sep	1
32		12 Dec	1
33	Branch 4 Total		38
34	Branch 5	07 Jul	31
35		09 Sep	1
36		11 Nov	1
37		12 Dec	3
38	Branch 5 Total		36
39	Branch 6	01 Jan	2
40		02 Feb	1
41		03 Mar	2
42		05 May	1
43		06 Jun	2
44		07 Jul	4
45		08 Aug	2
46		09 Sep	7
47		10 Oct	11
48		12 Dec	1
49	Branch 6 Total		33
50	Branch 7	01 Jan	2
51		03 Mar	11
52		04 Apr	1
53		06 Jun	1
54		07 Jul	3
55		10 Oct	3
56		11 Nov	4
57		12 Dec	8
58	Branch 7 Total		33
59	Branch 8	01 Jan	2
60		02 Feb	2
61		03 Mar	24
62		05 May	1
63		07 Jul	3
64	Branch 8 Total		32
65	Branch 9	02 Feb	15
66		03 Mar	14
67	Branch 9 Total		29
68	Branch 10	02 Feb	2
69		03 Mar	5
70		07 Jul	4
71		09 Sep	3
72		10 Oct	10
73		11 Nov	1
74		12 Dec	1
75	Branch 10 Total		26
76	Branch 11	02 Feb	1
77		03 Mar	20
78		09 Sep	3
79	Branch 11 Total		24
80	Branch 12	01 Jan	9
81		02 Feb	1
82		03 Mar	2
83		04 Apr	3
84		09 Sep	4
85		11 Nov	2
86		12 Dec	1
87	Branch 12 Total		22
88	Branch 13	01 Jan	4
89		02 Feb	2
90		03 Mar	4
91		04 Apr	1
92		05 May	3
93		08 Aug	2
94		10 Oct	1
95		11 Nov	5
96	Branch 13 Total		22
97	Branch 14	01 Jan	1
98		02 Feb	4
99		03 Mar	8
100		04 Apr	1
101		05 May	2
102		10 Oct	3
103		11 Nov	1
104		12 Dec	2
105	Branch 14 Total		22
106	Branch 15	12 Dec	20
107	Branch 15 Total		20
108	Branch 16	01 Jan	13
109		03 Mar	5
110		04 Apr	2
111	Branch 16 Total		20
112	Branch 17	03 Mar	14
113		04 Apr	1
114		09 Sep	2
115		10 Oct	1
116		12 Dec	1
117	Branch 17 Total		19
118	Branch 18	03 Mar	1
119		04 Apr	2
120		05 May	1
121		06 Jun	1
122		07 Jul	1
123		09 Sep	11
124		10 Oct	1
125	Branch 18 Total		18
126	Branch 20	01 Jan	1
127		03 Mar	10
128		06 Jun	2
129		08 Aug	4
130	Branch 20 Total		17
131	Branch 21	08 Aug	2
132		09 Sep	3
133		10 Oct	10
134		12 Dec	1
135	Branch 21 Total		16
Sheet1

 

[KRice]

Here is one approach. Tables structured like yours generally do not lend themselves well to this type of calculation. Rather than inserting branch totals in your data table, I would recommend eliminating those lines and filling in the branch column so that every line in your data table is clear...a branch, date, and count are shown. Then you can use some other other formulas to extract branch totals and to create a sorted list, as shown below. Note that I've changed some branch entries to create some "ties" to confirm that all of them appear in the sorted list. By the way, by adopting a "flat" table structure as shown here together with the other formulas, it doesn't matter where you place the most recent count data in the table...for example, you could append today's count results for all of the branches at the bottom of the data table, and navigating through the table to insert results for each of the branches is not necessary.

MrExcel20210212.xlsx
	A	B	C	D	E	F	G	H	I
1
2	COUNT Per Month				Total Counts			Top 10 Count
3	Branch Name	Month	COUNTA of Branch Name		Branch Name	Count		Branch Name	Count
4	Branch 1	1-Jan	2		Branch 1	67		Branch 11	124
5	Branch 1	2-Feb	2		Branch 2	49		Branch 16	124
6	Branch 1	3-Mar	10		Branch 3	41		Branch 21	124
7	Branch 1	4-Apr	5		Branch 4	38		Branch 1	67
8	Branch 1	5-May	2		Branch 5	36		Branch 2	49
9	Branch 1	6-Jun	6		Branch 6	33		Branch 3	41
10	Branch 1	7-Jul	8		Branch 7	33		Branch 4	38
11	Branch 1	8-Aug	1		Branch 8	32		Branch 5	36
12	Branch 1	9-Sep	6		Branch 9	29		Branch 6	33
13	Branch 1	10-Oct	6		Branch 10	26		Branch 7	33
14	Branch 1	11-Nov	15		Branch 11	124
15	Branch 1	12-Dec	4		Branch 12	22
16	Branch 2	1-Jan	35		Branch 13	22
17	Branch 2	5-May	6		Branch 14	22
18	Branch 2	7-Jul	6		Branch 15	20
19	Branch 2	9-Sep	2		Branch 16	124
20	Branch 3	5-May	1		Branch 17	19
21	Branch 3	6-Jun	1		Branch 18	18
22	Branch 3	8-Aug	8		Branch 19	0
23	Branch 3	9-Sep	4		Branch 20	17
24	Branch 3	10-Oct	6		Branch 21	124
25	Branch 3	11-Nov	17		Branch 22	0
26	Branch 3	12-Dec	4
27	Branch 4	3-Mar	36
28	Branch 4	9-Sep	1
29	Branch 4	12-Dec	1
30	Branch 5	7-Jul	31
31	Branch 5	9-Sep	1
32	Branch 5	11-Nov	1
33	Branch 5	12-Dec	3
34	Branch 6	1-Jan	2
35	Branch 6	2-Feb	1
36	Branch 6	3-Mar	2
37	Branch 6	5-May	1
38	Branch 6	6-Jun	2
39	Branch 6	7-Jul	4
40	Branch 6	8-Aug	2
41	Branch 6	9-Sep	7
42	Branch 6	10-Oct	11
43	Branch 6	12-Dec	1
44	Branch 7	1-Jan	2
45	Branch 7	3-Mar	11
46	Branch 7	4-Apr	1
47	Branch 7	6-Jun	1
48	Branch 7	7-Jul	3
49	Branch 7	10-Oct	3
50	Branch 7	11-Nov	4
51	Branch 7	12-Dec	8
52	Branch 8	1-Jan	2
53	Branch 8	2-Feb	2
54	Branch 8	3-Mar	24
55	Branch 8	5-May	1
56	Branch 8	7-Jul	3
57	Branch 9	2-Feb	15
58	Branch 9	3-Mar	14
59	Branch 10	2-Feb	2
60	Branch 10	3-Mar	5
61	Branch 10	7-Jul	4
62	Branch 10	9-Sep	3
63	Branch 10	10-Oct	10
64	Branch 10	11-Nov	1
65	Branch 10	12-Dec	1
66	Branch 11	2-Feb	101
67	Branch 11	3-Mar	20
68	Branch 11	9-Sep	3
69	Branch 12	1-Jan	9
70	Branch 12	2-Feb	1
71	Branch 12	3-Mar	2
72	Branch 12	4-Apr	3
73	Branch 12	9-Sep	4
74	Branch 12	11-Nov	2
75	Branch 12	12-Dec	1
76	Branch 13	1-Jan	4
77	Branch 13	2-Feb	2
78	Branch 13	3-Mar	4
79	Branch 13	4-Apr	1
80	Branch 13	5-May	3
81	Branch 13	8-Aug	2
82	Branch 13	10-Oct	1
83	Branch 13	11-Nov	5
84	Branch 14	1-Jan	1
85	Branch 14	2-Feb	4
86	Branch 14	3-Mar	8
87	Branch 14	4-Apr	1
88	Branch 14	5-May	2
89	Branch 14	10-Oct	3
90	Branch 14	11-Nov	1
91	Branch 14	12-Dec	2
92	Branch 15	12-Dec	20
93	Branch 16	1-Jan	13
94	Branch 16	3-Mar	5
95	Branch 16	4-Apr	106
96	Branch 17	3-Mar	14
97	Branch 17	4-Apr	1
98	Branch 17	9-Sep	2
99	Branch 17	10-Oct	1
100	Branch 17	12-Dec	1
101	Branch 18	3-Mar	1
102	Branch 18	4-Apr	2
103	Branch 18	5-May	1
104	Branch 18	6-Jun	1
105	Branch 18	7-Jul	1
106	Branch 18	9-Sep	11
107	Branch 18	10-Oct	1
108	Branch 20	1-Jan	1
109	Branch 20	3-Mar	10
110	Branch 20	6-Jun	2
111	Branch 20	8-Aug	4
112	Branch 21	8-Aug	2
113	Branch 21	9-Sep	3
114	Branch 21	10-Oct	10
115	Branch 21	12-Dec	109
Minx_Minxy

Cell Formulas
Range	Formula
H4:H13	H4	=INDEX($E$4:$E$25,AGGREGATE(15,6,(ROW($F$4:$F$25)-ROW($F$3))/($F$4:$F$25=I4),COUNTIF(I$4:I4,I4)))
I4:I13	I4	=LARGE($F$4:$F$25,ROWS(I$4:I4))
F4:F25	F4	=SUMIF($A$4:$A$181,E4,$C$4:$C$181)

 

[Minx_Minxy]

Thank you so much! That did the trick!

 

[KRice]

Great!...I'm happy to help.