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,

Improve this question
1
  • 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, 2020 at 16:35

1 Answer 1

Reset to default
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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .