0

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,

  • What tells you that starting at 23:00 and ending at 05:00 results in 6 hours (instead of 30, 54 etc...). Are there dates somewhere? If yes, then SUMIFS should do the job easily enough. – cybernetic.nomad Feb 14 at 16:35
0

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)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.