# Count Max Open Trades by Date and Pair

##### New Member
i have a field for the date the trade was opened and the date the trade was closed.
Is there a way I can calculate the maximum number of trades that were open at one time.
example

SNo D/M/Y D/M/Y
1. open date 1/1/2019 close date 3/1/2019
2. open date 2/1/2019 close date 3/1/2019
3. open date 3/2/2019 close date 4/1/2019

Max Pairs = 10 so the max Count is not Greater then 10
The max number of trades open would be 2 because the Trade 1 is open on 1/1/2019 & close on 3/1/2019 and trade 2 also open on Close with in the time of trade No1 so the Max open in this case = 2 Trades

i want the Count Max Open Orders in one time and its not grater then Max Pair ID.

see the attachment for more detail
the data is huge about 8000 rows

Thank you for any help

 Sr# Pair ID Created On Closed On Duration Max Trades Open in on time 1​ 1​ 22-Aug-17 6:00 AM 23-Aug-17 10:30 PM 1.69 2​ 2​ 2-Sep-17 10:45 AM 6-Sep-17 12:15 AM 3.56 1​ 4​ 3​ 1​ 2-Sep-17 11:00 AM 3-Sep-17 2:30 PM 1.15 TRUE​ 1​ 4​ 1​ 4-Sep-17 12:30 PM 6-Sep-17 10:00 AM 1.90 TRUE​ 1​ 5​ 2​ 5-Sep-17 6:30 AM 6-Sep-17 12:15 AM 0.74 TRUE​ 1​ 6​ 2​ 8-Sep-17 6:30 PM 15-Sep-17 7:30 PM 7.04 1​ 5​ 7​ 1​ 8-Sep-17 6:45 PM 18-Sep-17 12:00 PM 9.72 TRUE​ 1​ 8​ 2​ 14-Sep-17 4:45 PM 15-Sep-17 7:30 PM 1.11 TRUE​ 1​ 9​ 1​ 14-Sep-17 10:15 PM 18-Sep-17 12:00 PM 3.57 TRUE​ 1​ 10​ 2​ 15-Sep-17 4:30 PM 15-Sep-17 7:30 PM 0.13 TRUE​ 1​ 11​ 2​ 20-Sep-17 1:15 AM 20-Sep-17 7:00 PM 0.74 TRUE​ 1​ 12​ 1​ 21-Sep-17 8:30 PM 24-Sep-17 2:45 AM 2.26 TRUE​ 1​ 2​ 13​ 2​ 21-Sep-17 8:45 PM 22-Sep-17 9:45 AM 0.54 TRUE​ 1​ 14​ 2​ 29-Sep-17 7:45 AM 29-Sep-17 10:45 PM 0.63 15​ 1​ 15-Oct-17 1:15 PM 16-Oct-17 9:00 AM 0.82 16​ 2​ 15-Oct-17 3:15 PM 15-Oct-17 9:45 PM 0.27 17​ 2​ 2-Nov-17 6:30 AM 4-Nov-17 6:00 AM 1.98 18​ 1​ 8-Nov-17 11:45 PM 9-Nov-17 1:30 PM 0.57 19​ 1​ 10-Nov-17 4:30 PM 16-Nov-17 6:15 AM 5.57 20​ 2​ 10-Nov-17 6:00 PM 12-Nov-17 5:30 AM 1.48 21​ 1​ 17-Nov-17 12:45 PM 17-Nov-17 4:30 PM 0.16 22​ 1​ 21-Nov-17 8:45 AM 22-Nov-17 12:45 AM 0.67 23​ 4​ 28-Nov-17 5:30 PM 28-Nov-17 7:30 PM 0.08 24​ 3​ 30-Nov-17 12:45 AM 30-Nov-17 9:00 AM 0.34 25​ 1​ 30-Nov-17 12:45 AM 30-Nov-17 11:15 AM 0.44 26​ 2​ 30-Nov-17 12:45 AM 2-Dec-17 9:15 AM 2.35 27​ 4​ 30-Nov-17 12:45 AM 3-Dec-17 11:00 PM 3.93 28​ 1​ 4-Dec-17 2:45 AM 5-Dec-17 3:15 PM 1.52 29​ 2​ 15-Dec-17 7:45 AM 16-Dec-17 3:30 AM 0.82 30​ 2​ 18-Dec-17 6:00 AM 18-Dec-17 7:15 PM 0.55 31​ 1​ 18-Dec-17 6:15 AM 18-Dec-17 7:00 PM 0.53 32​ 1​ 19-Dec-17 3:15 PM 26-Dec-17 8:00 PM 7.20 33​ 4​ 19-Dec-17 3:30 PM 21-Dec-17 11:00 AM 1.81 34​ 3​ 21-Dec-17 8:15 PM 22-Dec-17 6:45 AM 0.44 35​ 2​ 21-Dec-17 8:15 PM 22-Dec-17 6:00 AM 0.41 36​ 5​ 21-Dec-17 8:15 PM 22-Dec-17 6:30 AM 0.43 37​ 4​ 21-Dec-17 8:15 PM 22-Dec-17 2:30 PM 0.76 38​ 1​ 22-Dec-17 8:45 AM 26-Dec-17 8:00 PM 4.47 39​ 4​ 22-Dec-17 12:30 PM 22-Dec-17 2:30 PM 0.08 40​ 2​ 24-Dec-17 4:45 AM 25-Dec-17 7:00 PM 1.59 41​ 4​ 24-Dec-17 5:00 AM 25-Dec-17 6:30 PM 1.56 42​ 3​ 24-Dec-17 5:15 AM 24-Dec-17 6:30 PM 0.55 43​ 1​ 28-Dec-17 8:00 AM 29-Dec-17 1:15 PM 1.22 44​ 2​ 28-Dec-17 8:00 AM 28-Dec-17 10:30 PM 0.60 45​ 5​ 28-Dec-17 8:00 AM 29-Dec-17 1:00 PM 1.21 46​ 4​ 28-Dec-17 8:00 AM 29-Dec-17 8:45 AM 1.03 47​ 2​ 30-Dec-17 7:15 AM 1-Jan-18 5:15 AM 1.92 48​ 4​ 30-Dec-17 7:30 AM 31-Dec-17 7:45 AM 1.01 49​ 2​ 8-Jan-18 7:45 PM 9-Jan-18 6:00 AM 0.43 50​ 1​ 8-Jan-18 8:00 PM 9-Jan-18 3:15 AM 0.30 51​ 4​ 8-Jan-18 8:00 PM 9-Jan-18 1:15 AM 0.22 52​ 1​ 10-Jan-18 1:30 PM 11-Jan-18 6:45 AM 0.72 53​ 1​ 11-Jan-18 8:45 AM 12-Jan-18 12:00 AM 0.64 54​ 4​ 11-Jan-18 9:00 AM 12-Jan-18 12:15 PM 1.14 55​ 2​ 11-Jan-18 9:30 AM 11-Jan-18 9:45 PM 0.51 56​ 1​ 14-Jan-18 9:45 AM 16-Jan-18 12:00 AM 1.59 57​ 2​ 14-Jan-18 5:15 PM 15-Jan-18 7:30 AM 0.59 58​ 3​ 16-Jan-18 6:45 AM 16-Jan-18 4:45 PM 0.42 59​ 1​ 16-Jan-18 6:45 AM 17-Jan-18 4:30 AM 0.91 60​ 2​ 16-Jan-18 6:45 AM 18-Jan-18 5:30 PM 2.45 61​ 5​ 16-Jan-18 1:00 PM 17-Jan-18 5:15 AM 0.68 62​ 3​ 16-Jan-18 3:15 PM 16-Jan-18 4:45 PM 0.06 63​ 2​ 17-Jan-18 2:30 AM 18-Jan-18 5:30 PM 1.63 64​ 5​ 17-Jan-18 3:00 AM 17-Jan-18 5:15 AM 0.09 65​ 1​ 17-Jan-18 3:30 AM 17-Jan-18 4:30 AM 0.04 66​ 4​ 17-Jan-18 3:30 AM 17-Jan-18 7:00 AM 0.15 67​ 1​ 22-Jan-18 6:30 PM 24-Jan-18 4:30 AM 1.42 68​ 2​ 22-Jan-18 6:30 PM 24-Jan-18 7:30 PM 2.04 69​ 5​ 22-Jan-18 6:30 PM 24-Jan-18 4:30 AM 1.42 70​ 4​ 22-Jan-18 6:30 PM 23-Jan-18 10:00 PM 1.15 71​ 3​ 26-Jan-18 1:15 PM 27-Jan-18 3:30 AM 0.59 72​ 1​ 26-Jan-18 1:15 PM 27-Jan-18 3:15 AM 0.58 73​ 2​ 26-Jan-18 1:15 PM 28-Jan-18 10:15 AM 1.88 74​ 4​ 26-Jan-18 1:15 PM 27-Jan-18 3:00 AM 0.57 75​ 5​ 26-Jan-18 1:30 PM 27-Jan-18 4:30 PM 1.13 76​ 1​ 29-Jan-18 6:30 AM 14-Feb-18 5:15 PM 16.45 77​ 2​ 30-Jan-18 7:00 AM 30-Jan-18 6:00 PM 0.46 78​ 2​ 30-Jan-18 11:15 PM 1-Feb-18 1:15 PM 1.58 79​ 3​ 1-Feb-18 1:45 PM 2-Feb-18 7:00 PM 1.22 80​ 4​ 1-Feb-18 1:45 PM 2-Feb-18 7:00 PM 1.22 81​ 1​ 2-Feb-18 12:45 AM 14-Feb-18 5:15 PM 12.69 82​ 4​ 2-Feb-18 2:15 PM 2-Feb-18 7:00 PM 0.20 83​ 3​ 2-Feb-18 5:15 PM 2-Feb-18 7:00 PM 0.07 84​ 2​ 4-Feb-18 5:15 PM 7-Feb-18 1:15 AM 2.33 85​ 1​ 5-Feb-18 11:00 PM 14-Feb-18 5:15 PM 8.76 86​ 2​ 6-Feb-18 12:30 AM 7-Feb-18 1:15 AM 1.03 87​ 4​ 15-Feb-18 3:15 PM 16-Feb-18 8:30 PM 1.22 88​ 4​ 18-Feb-18 9:00 AM 19-Feb-18 11:00 PM 1.58 89​ 2​ 18-Feb-18 11:00 AM 18-Feb-18 9:45 PM 0.45 90​ 2​ 21-Feb-18 3:45 AM 24-Apr-18 6:30 PM 62.61 91​ 4​ 21-Feb-18 4:00 AM 26-Feb-18 5:30 PM 5.56 92​ 3​ 21-Feb-18 4:15 AM 26-Feb-18 7:15 PM 5.63 93​ 1​ 21-Feb-18 4:15 AM 21-Apr-18 8:30 AM 59.18 94​ 4​ 6-Mar-18 1:30 AM 16-Apr-18 5:30 AM 41.17 95​ 3​ 6-Mar-18 3:45 PM 13-Mar-18 6:45 PM 7.13 96​ 5​ 7-Mar-18 10:00 PM 8-Mar-18 4:00 AM 0.25 97​ 2​ 9-Mar-18 3:45 AM 24-Apr-18 6:30 PM 46.61 98​ 4​ 9-Mar-18 7:15 AM 16-Apr-18 5:30 AM 37.93 99​ 1​ 9-Mar-18 9:15 AM 21-Apr-18 8:30 AM 42.97 100​ 3​ 9-Mar-18 9:30 AM 13-Mar-18 6:45 PM 4.39 101​ 4​ 15-Mar-18 2:30 AM 16-Apr-18 5:30 AM 32.13 102​ 2​ 18-Mar-18 12:15 AM 24-Apr-18 6:30 PM 37.76 103​ 5​ 26-Mar-18 4:30 PM 3-Apr-18 11:45 PM 8.30 104​ 5​ 30-Mar-18 1:45 AM 3-Apr-18 11:45 PM 4.92 105​ 1​ 30-Mar-18 5:30 AM 21-Apr-18 8:30 AM 22.13 106​ 5​ 4-Apr-18 2:45 PM 13-Apr-18 4:00 AM 8.55 107​ 4​ 6-Apr-18 4:00 PM 16-Apr-18 5:30 AM 9.56 108​ 3​ 9-Apr-18 3:30 PM 12-Apr-18 5:00 AM 2.56 109​ 4​ 18-Apr-18 12:15 AM 18-Apr-18 12:30 PM 0.51 110​ 4​ 21-Apr-18 1:30 PM 23-Apr-18 1:00 PM 1.98 111​ 6​ 21-Apr-18 3:00 PM 21-Apr-18 10:00 PM 0.29 112​ 5​ 21-Apr-18 4:15 PM 22-Apr-18 1:45 AM 0.40 113​ 6​ 25-Apr-18 5:45 AM 27-Apr-18 5:45 PM 2.50 114​ 2​ 25-Apr-18 9:00 AM 27-Apr-18 3:30 PM 2.27 115​ 4​ 25-Apr-18 9:00 AM 29-Apr-18 1:45 PM 4.20 116​ 5​ 25-Apr-18 10:00 AM 29-Apr-18 1:30 PM 4.15 117​ 6​ 28-Apr-18 1:45 AM 28-Apr-18 6:00 PM 0.68 118​ 2​ 28-Apr-18 1:45 AM 28-Apr-18 11:00 PM 0.89 119​ 3​ 29-Apr-18 2:00 PM 30-Apr-18 8:00 AM 0.75 120​ 2​ 29-Apr-18 2:00 PM 30-Apr-18 9:00 AM 0.79 121​ 6​ 29-Apr-18 2:15 PM 29-Apr-18 8:30 PM 0.26 122​ 6​ 1-May-18 5:45 AM 1-May-18 7:45 PM 0.58 123​ 3​ 1-May-18 5:45 AM 3-May-18 11:00 AM 2.22 124​ 1​ 1-May-18 5:45 AM 2-May-18 8:15 AM 1.10 125​ 2​ 1-May-18 5:45 AM 2-May-18 5:15 PM 1.48 126​ 4​ 1-May-18 5:45 AM 2-May-18 8:30 AM 1.11 127​ 6​ 6-May-18 11:15 AM 2-Apr-19 12:15 PM 331.04 128​ 2​ 6-May-18 11:15 AM 15-May-19 9:00 PM 374.41 129​ 4​ 6-May-18 11:15 AM 22-Jun-19 6:15 PM 412.29 130​ 1​ 6-May-18 11:30 AM 20-Jul-18 9:45 PM 75.43 131​ 6​ 11-May-18 12:30 PM 2-Apr-19 12:15 PM 325.99 132​ 4​ 11-May-18 12:45 PM 22-Jun-19 6:15 PM 407.23 133​ 6​ 23-May-18 1:00 PM 2-Apr-19 12:15 PM 313.97 134​ 2​ 23-May-18 8:45 PM 15-May-19 9:00 PM 357.01 135​ 1​ 24-May-18 1:30 PM 20-Jul-18 9:45 PM 57.34 136​ 4​ 24-May-18 1:30 PM 22-Jun-19 6:15 PM 394.20 137​ 5​ 28-May-18 11:00 AM 29-May-18 4:00 PM 1.21 138​ 5​ 30-May-18 8:00 PM 31-May-18 6:15 PM 0.93 139​ 8​ 10-Jun-18 6:00 AM 3-Jul-18 8:45 AM 23.11 140​ 5​ 10-Jun-18 6:00 AM 6-Mar-19 3:45 PM 269.41 141​ 3​ 10-Jun-18 10:45 PM 11-Jun-18 7:15 PM 0.85 142​ 10​ 13-Jun-18 12:15 AM 15-Jun-18 6:45 AM 2.27 143​ 2​ 13-Jun-18 4:30 AM 15-May-19 9:00 PM 336.69 144​ 8​ 13-Jun-18 2:45 PM 3-Jul-18 8:45 AM 19.75 145​ 9​ 20-Jun-18 6:30 AM 20-Jun-18 3:30 PM 0.38 146​ 7​ 22-Jun-18 12:15 PM 2-Jul-18 6:30 PM 10.26 147​ 9​ 22-Jun-18 12:15 PM 24-Jun-18 10:30 PM 2.43 148​ 5​ 22-Jun-18 3:00 PM 6-Mar-19 3:45 PM 257.03 149​ 6​ 23-Jun-18 2:15 AM 2-Apr-19 12:15 PM 283.42 150​ 1​ 23-Jun-18 2:15 AM 20-Jul-18 9:45 PM 27.81 151​ 8​ 23-Jun-18 2:15 AM 3-Jul-18 8:45 AM 10.27 152​ 4​ 23-Jun-18 2:15 AM 22-Jun-19 6:15 PM 364.67 153​ 9​ 24-Jun-18 10:00 AM 24-Jun-18 10:30 PM 0.52 154​ 9​ 9-Jul-18 10:15 PM 16-Jul-18 4:30 PM 6.76 155​ 7​ 10-Jul-18 11:15 AM 16-Jul-18 4:30 PM 6.22 156​ 9​ 13-Jul-18 3:45 AM 16-Jul-18 4:30 PM 3.53 157​ 7​ 20-Jul-18 12:00 PM 26-Jul-18 2:00 PM 6.08 158​ 1​ 25-Jul-18 8:45 PM 26-Jul-18 8:15 AM 0.48 159​ 7​ 27-Jul-18 2:30 AM 30-Jul-18 1:00 PM 3.44 160​ 1​ 27-Jul-18 2:45 AM 1-Sep-18 8:15 PM 36.73 161​ 10​ 27-Jul-18 2:45 AM 27-Jul-18 4:30 PM 0.57 162​ 10​ 30-Jul-18 10:45 PM 21-Sep-18 1:15 PM 52.60 163​ 7​ 1-Aug-18 4:30 AM 4-Aug-18 1:30 AM 2.88 164​ 8​ 3-Aug-18 5:30 AM 3-Aug-18 9:45 AM 0.18 165​ 5​ 8-Aug-18 1:15 AM 6-Mar-19 3:45 PM 210.60 166​ 10​ 8-Aug-18 9:15 AM 21-Sep-18 1:15 PM 44.17 167​ 4​ 8-Aug-18 9:45 PM 22-Jun-19 6:15 PM 317.85 168​ 1​ 8-Aug-18 11:45 PM 1-Sep-18 8:15 PM 23.85 169​ 3​ 11-Aug-18 2:15 AM 15-Aug-18 7:30 AM 4.22 170​ 7​ 11-Aug-18 2:15 AM 15-Aug-18 9:15 PM 4.79 171​ 2​ 11-Aug-18 5:30 PM 15-May-19 9:00 PM 277.15 172​ 9​ 11-Aug-18 7:00 PM 11-Aug-18 10:30 PM 0.15 173​ 6​ 14-Aug-18 6:30 AM 2-Apr-19 12:15 PM 231.24 174​ 3​ 14-Aug-18 7:00 AM 15-Aug-18 7:30 AM 1.02 175​ 7​ 14-Aug-18 7:00 AM 15-Aug-18 9:15 PM 1.59 176​ 9​ 14-Aug-18 7:15 AM 14-Aug-18 1:15 PM 0.25 177​ 3​ 5-Sep-18 3:00 PM 9-Jan-19 12:30 PM 125.90 178​ 1​ 5-Sep-18 3:00 PM 15-Oct-18 12:00 PM 39.88 179​ 7​ 5-Sep-18 3:00 PM 22-Sep-18 6:15 AM 16.64 180​ 8​ 5-Sep-18 3:00 PM 14-Sep-18 8:00 AM 8.71 181​ 9​ 5-Sep-18 3:00 PM 7-Sep-18 4:30 AM 1.56

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I couldn't understand what you meant by maxpairs and maxcount.... And I needed excel365 to do this easily (I hope you have it), but here is a segment of my sheet that calculates the stuff from your 181 records here.

MrExcelPlayground2.xlsm
ABCDEFGHIJKLM
1Max open at onceWhen that was happening
2Sr#Pair IDCreated OnClosed OnDurationMax Trades Open in on time8/22/2017 6:001268/14/2018 7:3026
3118/22/2017 6:008/23/2017 22:301.698/22/2017 6:1518/14/2018 7:4526
4229/2/2017 10:459/6/2017 0:153.56148/22/2017 6:3018/14/2018 8:0026
5319/2/2017 11:009/3/2017 14:301.15TRUE18/22/2017 6:4518/14/2018 8:1526
6419/4/2017 12:309/6/2017 10:001.9TRUE18/22/2017 7:0018/14/2018 8:3026
7529/5/2017 6:309/6/2017 0:150.74TRUE18/22/2017 7:1518/14/2018 8:4526
8629/8/2017 18:309/15/2017 19:307.04158/22/2017 7:3018/14/2018 9:0026
9719/8/2017 18:459/18/2017 12:009.72TRUE18/22/2017 7:4518/14/2018 9:1526
10829/14/2017 16:459/15/2017 19:301.11TRUE18/22/2017 8:0018/14/2018 9:3026
11919/14/2017 22:159/18/2017 12:003.57TRUE18/22/2017 8:1518/14/2018 9:4526
121029/15/2017 16:309/15/2017 19:300.13TRUE18/22/2017 8:3018/14/2018 10:0026
131129/20/2017 1:159/20/2017 19:000.74TRUE18/22/2017 8:4518/14/2018 10:1526
141219/21/2017 20:309/24/2017 2:452.26TRUE128/22/2017 9:0018/14/2018 10:3026
151329/21/2017 20:459/22/2017 9:450.54TRUE18/22/2017 9:1518/14/2018 10:4526
161429/29/2017 7:459/29/2017 22:450.638/22/2017 9:3018/14/2018 11:0026
1715110/15/2017 13:1510/16/2017 9:000.828/22/2017 9:4518/14/2018 11:1526
1816210/15/2017 15:1510/15/2017 21:450.278/22/2017 10:0018/14/2018 11:3026
1917211/2/2017 6:3011/4/2017 6:001.988/22/2017 10:1518/14/2018 11:4526
2018111/8/2017 23:4511/9/2017 13:300.578/22/2017 10:3018/14/2018 12:0026
2119111/10/2017 16:3011/16/2017 6:155.578/22/2017 10:4518/14/2018 12:1526
2220211/10/2017 18:0011/12/2017 5:301.488/22/2017 11:0018/14/2018 12:3026
2321111/17/2017 12:4511/17/2017 16:300.168/22/2017 11:1518/14/2018 12:4526
2422111/21/2017 8:4511/22/2017 0:450.678/22/2017 11:3018/14/2018 13:0026
2523411/28/2017 17:3011/28/2017 19:300.088/22/2017 11:4518/14/2018 13:1526
2624311/30/2017 0:4511/30/2017 9:000.348/22/2017 12:001
Sheet42
Cell Formulas
RangeFormula
I2:I64274I2=SEQUENCE((MAX(D3:D183)-MIN(C3:C183))*24*4,1,MIN(C3:C183),1/24/4)
K2K2=MAX(J2:J64274)
L2:M25L2=FILTER(I2:J64274,J2:J64274=K2,"")
J2:J26J2=SUMPRODUCT(--(I2>=\$C\$3:\$C\$183),--(I2<=\$D\$3:\$D\$183))
Dynamic array formulas.

It's set up by going through 15 minute intervals and counting how many trades are open for each 15 minutes from the earliest date to the latest date of your whole set. If everything could be assumed to be just whole days (no time), then this could be done with older versions without too much trouble. I good macro would make quick enough work of it too.

Replies
1
Views
183
Replies
1
Views
283
Replies
11
Views
285
Replies
3
Views
168
Replies
1
Views
86

1,219,800
Messages
6,150,322
Members
450,951
Latest member
kh198

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

### Which adblocker are you using?

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

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