Countifs or Sumproduct to summarise data instead of a Pivot Table

[slamanager]

having issues when building formulas to lookup data for a certain date and time range.

E.g. need the count of tickets raised in a day in a certain time range 2/1/2015 from 8:00:00 am to 8:30:00 am

I have the date and the time ranges in seperate cells (3) im using to query the data ive tried COUNTIFS but when combining the refrences i get "0" displayed.

DATA
ticket numbers in Col A
Full date and time combined in Col B ( 2/01/2015 12:50:13 PM )

Summary
first date of the month in D5 ( 01/01/2015 )
and time ranges starting in C6 ( 0:00:00 ) C7 ( 0:30:00 )

I need to do thi sby formulas because i need to see where data is missing from the source system

 

[Bob Phillips]

Something like

=COUNTIFS($B$2:$B$40,">="&$D$1+$C$6,$B$2:$B$40,"<="&$D$1+$C$7)