Count top 10 per frequency during the year

Minx_Minxy

New Member
Joined
Jul 13, 2015
Messages
10
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
ABCDEFGH
2COUNT Per MonthTop 10 Count
3Branch NameMonthCOUNTA of Branch NameBranch NameCount
4Branch 101 Jan2
502 Feb2
603 Mar10
704 Apr5
805 May2
906 Jun6
1007 Jul8
1108 Aug1
1209 Sep6
1310 Oct6
1411 Nov15
1512 Dec4
16Branch 1 Total67
17Branch 201 Jan35
1805 May6
1907 Jul6
2009 Sep2
21Branch 2 Total49
22Branch 305 May1
2306 Jun1
2408 Aug8
2509 Sep4
2610 Oct6
2711 Nov17
2812 Dec4
29Branch 3 Total41
30Branch 403 Mar36
3109 Sep1
3212 Dec1
33Branch 4 Total38
34Branch 507 Jul31
3509 Sep1
3611 Nov1
3712 Dec3
38Branch 5 Total36
39Branch 601 Jan2
4002 Feb1
4103 Mar2
4205 May1
4306 Jun2
4407 Jul4
4508 Aug2
4609 Sep7
4710 Oct11
4812 Dec1
49Branch 6 Total33
50Branch 701 Jan2
5103 Mar11
5204 Apr1
5306 Jun1
5407 Jul3
5510 Oct3
5611 Nov4
5712 Dec8
58Branch 7 Total33
59Branch 801 Jan2
6002 Feb2
6103 Mar24
6205 May1
6307 Jul3
64Branch 8 Total32
65Branch 902 Feb15
6603 Mar14
67Branch 9 Total29
68Branch 1002 Feb2
6903 Mar5
7007 Jul4
7109 Sep3
7210 Oct10
7311 Nov1
7412 Dec1
75Branch 10 Total26
76Branch 1102 Feb1
7703 Mar20
7809 Sep3
79Branch 11 Total24
80Branch 1201 Jan9
8102 Feb1
8203 Mar2
8304 Apr3
8409 Sep4
8511 Nov2
8612 Dec1
87Branch 12 Total22
88Branch 1301 Jan4
8902 Feb2
9003 Mar4
9104 Apr1
9205 May3
9308 Aug2
9410 Oct1
9511 Nov5
96Branch 13 Total22
97Branch 1401 Jan1
9802 Feb4
9903 Mar8
10004 Apr1
10105 May2
10210 Oct3
10311 Nov1
10412 Dec2
105Branch 14 Total22
106Branch 1512 Dec20
107Branch 15 Total20
108Branch 1601 Jan13
10903 Mar5
11004 Apr2
111Branch 16 Total20
112Branch 1703 Mar14
11304 Apr1
11409 Sep2
11510 Oct1
11612 Dec1
117Branch 17 Total19
118Branch 1803 Mar1
11904 Apr2
12005 May1
12106 Jun1
12207 Jul1
12309 Sep11
12410 Oct1
125Branch 18 Total18
126Branch 2001 Jan1
12703 Mar10
12806 Jun2
12908 Aug4
130Branch 20 Total17
131Branch 2108 Aug2
13209 Sep3
13310 Oct10
13412 Dec1
135Branch 21 Total16
Sheet1
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHI
1
2COUNT Per MonthTotal CountsTop 10 Count
3Branch NameMonthCOUNTA of Branch NameBranch NameCountBranch NameCount
4Branch 11-Jan2Branch 167Branch 11124
5Branch 12-Feb2Branch 249Branch 16124
6Branch 13-Mar10Branch 341Branch 21124
7Branch 14-Apr5Branch 438Branch 167
8Branch 15-May2Branch 536Branch 249
9Branch 16-Jun6Branch 633Branch 341
10Branch 17-Jul8Branch 733Branch 438
11Branch 18-Aug1Branch 832Branch 536
12Branch 19-Sep6Branch 929Branch 633
13Branch 110-Oct6Branch 1026Branch 733
14Branch 111-Nov15Branch 11124
15Branch 112-Dec4Branch 1222
16Branch 21-Jan35Branch 1322
17Branch 25-May6Branch 1422
18Branch 27-Jul6Branch 1520
19Branch 29-Sep2Branch 16124
20Branch 35-May1Branch 1719
21Branch 36-Jun1Branch 1818
22Branch 38-Aug8Branch 190
23Branch 39-Sep4Branch 2017
24Branch 310-Oct6Branch 21124
25Branch 311-Nov17Branch 220
26Branch 312-Dec4
27Branch 43-Mar36
28Branch 49-Sep1
29Branch 412-Dec1
30Branch 57-Jul31
31Branch 59-Sep1
32Branch 511-Nov1
33Branch 512-Dec3
34Branch 61-Jan2
35Branch 62-Feb1
36Branch 63-Mar2
37Branch 65-May1
38Branch 66-Jun2
39Branch 67-Jul4
40Branch 68-Aug2
41Branch 69-Sep7
42Branch 610-Oct11
43Branch 612-Dec1
44Branch 71-Jan2
45Branch 73-Mar11
46Branch 74-Apr1
47Branch 76-Jun1
48Branch 77-Jul3
49Branch 710-Oct3
50Branch 711-Nov4
51Branch 712-Dec8
52Branch 81-Jan2
53Branch 82-Feb2
54Branch 83-Mar24
55Branch 85-May1
56Branch 87-Jul3
57Branch 92-Feb15
58Branch 93-Mar14
59Branch 102-Feb2
60Branch 103-Mar5
61Branch 107-Jul4
62Branch 109-Sep3
63Branch 1010-Oct10
64Branch 1011-Nov1
65Branch 1012-Dec1
66Branch 112-Feb101
67Branch 113-Mar20
68Branch 119-Sep3
69Branch 121-Jan9
70Branch 122-Feb1
71Branch 123-Mar2
72Branch 124-Apr3
73Branch 129-Sep4
74Branch 1211-Nov2
75Branch 1212-Dec1
76Branch 131-Jan4
77Branch 132-Feb2
78Branch 133-Mar4
79Branch 134-Apr1
80Branch 135-May3
81Branch 138-Aug2
82Branch 1310-Oct1
83Branch 1311-Nov5
84Branch 141-Jan1
85Branch 142-Feb4
86Branch 143-Mar8
87Branch 144-Apr1
88Branch 145-May2
89Branch 1410-Oct3
90Branch 1411-Nov1
91Branch 1412-Dec2
92Branch 1512-Dec20
93Branch 161-Jan13
94Branch 163-Mar5
95Branch 164-Apr106
96Branch 173-Mar14
97Branch 174-Apr1
98Branch 179-Sep2
99Branch 1710-Oct1
100Branch 1712-Dec1
101Branch 183-Mar1
102Branch 184-Apr2
103Branch 185-May1
104Branch 186-Jun1
105Branch 187-Jul1
106Branch 189-Sep11
107Branch 1810-Oct1
108Branch 201-Jan1
109Branch 203-Mar10
110Branch 206-Jun2
111Branch 208-Aug4
112Branch 218-Aug2
113Branch 219-Sep3
114Branch 2110-Oct10
115Branch 2112-Dec109
Minx_Minxy
Cell Formulas
RangeFormula
H4:H13H4=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:I13I4=LARGE($F$4:$F$25,ROWS(I$4:I4))
F4:F25F4=SUMIF($A$4:$A$181,E4,$C$4:$C$181)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,172
Messages
5,640,580
Members
417,151
Latest member
ChickenTenderer

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
Top