IF Formula with date and time variable using y axis (date) and x axis (time) as reference

Dear All,

Having a little issue with an IF formula in a time format but I also have to include dates in my formula.

I have attached the input file (raw data on sheet 1 and output format on sheet 2).

On table 1 (sheet 2), I would like to count the number of “CONNECTED” calls (column E) that are made within one hour intervals (column M) and within certain from sheet 1 . I want to use the date format for my rows and the time format for my column to simplify but not to sure how to go about that as well. I assume one can just show how an example in one cell and I can do the rest.

I have attached the file for ease of understanding. My output file is on the next sheet labeled “output”

Would really appreciate the help.

Kindest Regards
Muammar

Put in C6 and copy across and down

=SUMPRODUCT((Input!$E$2:$E$95="CONNECTED")*(INT(Input!$F$2:$F$95)=Output!C$5)*(HOUR(Input!$F$2:$F$95)=HOUR(Output!$B6)))

Note that I changed the dates in Table 1 headings to actual dates (not text); Dates can be formatted in numerous ways but should always be entered in date format (01/07/2015 etc) so they can "processed" by formula.

Just copy Table 1 headings to Table 2.

Thank you very much for the help John. Very helpful.

Can you mark this as solved if you have the solution.