QUESTION :
I have a list of contracts that run for a month, each line is a separate contract that is active during this times. I need to know what is the sum value between 00:00 and 01:00 (which is 8). What formula can I use. I tried sumif but the problem is that I can’t figure out how to include the values for lines as 00:00 to 00:00 which means this contract runs for 24 hours and also contract starting at 23:00 preceding day till 05:00.
Jan
start end value
00:00 05:00 5
05:00 10:00 5
23:00 05:00 2
00:00 00:00 1
Thank you,
ANSWER :
Perhaps, this formula can help you:
=SUMPRODUCT((($A$2:$A$5>=0)*(--($A$2:$A$5<(1/24)+($A$2:$A$5>$B$2:$B$5))>0)*(--(($B$2:$B$5>=1/24)+($A$2:$A$5<1/24))>0))*$C$2:$C$5)
