Sum(if by the hour

[dmheller]

All,
I am trying to do a sum if by the hour and all I can get with dummy data is by the day. Any one know if I can do this? Equation I am using is
=SUM(IF(ISNUMBER('Line 5'!$C$5:$C$44903),IF(INT('Line 5'!$C$5:$C$44903)=INT(B9),--(('Line 5'!$K$5:$K$44703>220)+('Line 5'!$L$5:$L$44703>220)+('Line 5'!$M$5:$M$44703>220)+('Line 5'!$N$5:$N$44703>220)>0)*('Line 5'!$J$5:$J$44703=1))/60*60))

Line 5 C is dates by the min
B9 is a date with an hour time stamp
all other are numbers.
Also, if you know what I need to change to get rid of my 60*60 in there that would be great, it gets mad when I delete it. I am sure I am missing a ( or , just don't know.

 

[barry houdini]

So if B9 is 11/11/2017 11:00 then do you need it to match with both the date and hour in column C, i.e. for my example any time between 11:00 and 11:59 on 11/11?

If so try using FLOOR like this

=SUM(IF(ISNUMBER('Line 5'!$C$5:$C$44903),IF(FLOOR('Line 5'!$C$5:$C$44903,1/24)=FLOOR(B9,1/24),--(('Line 5'!$K$5:$K$44703>220)+('Line 5'!$L$5:$L$44703>220)+('Line 5'!$M$5:$M$44703>220)+('Line 5'!$N$5:$N$44703>220)>0)*('Line 5'!$J$5:$J$44703=1))))