Sumproduct Formula Not Working Consistently - Date Issue

I'm trying to build a spreadsheet that will eventually calculate the average occupancy rate based on periods of time. Eventually I would like the average occupancy for all objects, per hour, the average occupancy of all objects from 9 AM to 12 PM, etc. I started by using Sumproduct to find the occupancy per 15 minute period which is the smallest time period of the data. There is only one row in the data for each object and each 15 minute time period.

The problem I'm having is the Sumproduct formula works about half the time. The other half are not getting any results. I've tried a few things, including formatting the data the same, splitting the date and time into seperate columns. I get the same results and it is always the same rows that give no results. I can get results on the rows that previously didn't get results if I manually type the date and time into the hour worksheet.

I just don't understand why some work and some don't. I've attached the spreadsheet. The cells with zero are the ones not working, specifically 1:45 AM, 2:30 AM, and 4:45 AM are three examples.

Anyone have any thoughts.

It has to do with the precision in Excel's calculation... the formula result you have is not exactly the same as the date translation Excel uses.

Try this, in C5, to get better accuracy:

=SUMPRODUCT--(ROUND(Data!$A$2:$A$1326,6)=ROUND($A5,6)),--(Data!$D$2:$D$1326=C$4),(Data!$N$2:$N$1326))

copied down and across the table.

No the 6 doesn't mean that...

Excel stores date/time as actual numbers... where the date is the part before the decimal and the time is everything after the decimal...

So I just round the whole time to 6 decimals..which should change the end result.