Complicated countif(s)

L

ludo2016

New Member
Joined
May 9, 2016
Messages
10
I want to count the number of events are taking place on each day over a 2+ year period. We have the start and end date for each event and there are almost 2000 events. In essence, I think the criteria range essentially becomes in this case A2:B5, and we're looking counting the number of times a date appears in four ranges. Any ideas how I can do this? [TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="width: 111"]Event start[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="width: 111"]event end[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Number of events[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 222"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]03/07/2016 07:33[/TD]
[TD="class: xl65, width: 111, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]14/07/2016 04:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]03/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]04/07/2016 04:56[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/07/2016 03:30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]04/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]06/07/2016 02:11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]13/07/2016 10:08[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]05/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]07/07/2016 03:16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]10/07/2016 22:23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]06/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]07/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]08/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]09/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]10/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]11/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]12/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]13/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]14/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Please can someone help. Appreciate it may be easier to input a html screenshot and went thru the instruction to do this via add-in but the generate html button doesn't work. Perhaps this simple copy paste is good enough. Any advice on how to do this, much appreciated.

[TABLE="width: 425"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Event start[/TD]
[TD]event end[/TD]
[TD]Date[/TD]
[TD="colspan: 2"]Number of events[/TD]
[/TR]
[TR]
[TD="align: right"]03/07/2016 07:33[/TD]
[TD="align: right"]14/07/2016 04:49[/TD]
[TD="align: right"]03/07/2016[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/07/2016 04:56[/TD]
[TD="align: right"]12/07/2016 03:30[/TD]
[TD="align: right"]04/07/2016[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/07/2016 02:11[/TD]
[TD="align: right"]13/07/2016 10:08[/TD]
[TD="align: right"]05/07/2016[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/07/2016 03:16[/TD]
[TD="align: right"]10/07/2016 22:23[/TD]
[TD="align: right"]06/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]09/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Maybe:

ABCD
Event StartEvent EndDate# of Events

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7/3/2016 7:33[/TD]
[TD="align: right"]7/14/2016 4:49[/TD]
[TD="align: right"]7/3/2016[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7/4/2016 4:56[/TD]
[TD="align: right"]7/12/2016 3:30[/TD]
[TD="align: right"]7/4/2016[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7/6/2016 2:11[/TD]
[TD="align: right"]7/13/2016 10:08[/TD]
[TD="align: right"]7/5/2016[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/7/2016 3:16[/TD]
[TD="align: right"]7/10/2016 22:23[/TD]
[TD="align: right"]7/6/2016[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/7/2016[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/8/2016[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/9/2016[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/10/2016[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/11/2016[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/12/2016[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/13/2016[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/14/2016[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUMPRODUCT(--(C2>=INT($A$2:$A$20)),--(C2<=$B$2:$B$20))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA: delete all rows where value (time) is less than 1 minute
2
Replies
13
Views
219
RobP
R
L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
Round down date / time field to nearest hour only
Replies
2
Views
670
surkdidat
S
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
317
dshtesy
D
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
139
SFCChase
SFCChase

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

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
Back
Top