CODEWITHSUNDEEP
excelexcel-formulaexcel-2016
Iruoma
Iruoma

Reputation: 27

EXCEL - Formula to sum total period of multiple durations without duplicating overlapping time periods

Machine_ID  STARTDATE   ENDDATE
Machine 1   23-Apr-10   8-Jul-13
Machine 1   16-Jun-10   10-Jul-10
Machine 1   15-Aug-12   5-Oct-12
Machine 1   9-Jul-13    2-Jan-14
Machine 1   31-Dec-13   3-Jan-14
Machine 2   28-Mar-13   14-Apr-13
Machine 2   12-Jun-13   27-Jul-13
Machine 2   24-Jul-13   28-Oct-13
Machine 2   24-Oct-13   27-Oct-13

I want to calculate the total time by each machine without duplicating overlapping time periods. So far this array formula (ctrl+shift+enter) does it perfectly:

=SUM(--(COUNTIF(B$2:B$5,"<"&ROW(A:A))-COUNTIF(C$2:C$5,"<"&ROW(A:A))>0))

But i need to tweak it to be able to do it for each machine (there are hundreds of machines)

I saw this formula that calculates for each one (first you fill out rows with unique machine numbers and then it does the calculation, but this only calculates overlapping times

=SUMPRODUCT(--($A$2:$A$36=$I2),--($C$2:$C$36>$B$3:$B$37),--($B$3:$B$37<>""), 
($C$2:$C$36-$B$3:$B$37)-($C$2:$C$36-$C$3:$C$37)*($C$2:$C$36>$C$3:$C$37)* 
($C$3:$C$37<>0))

This is column I

Machine 1
Machine 2
Machine 3

Thank you!

Upvotes: 0

Views: 813

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use COUNTIFS and add the criteria for the machine:

=SUMPRODUCT(--(COUNTIFS(B$2:B$10,"<"&ROW($A$35000:$A$60000),$A$2:$A$10,I1)-COUNTIFS(C$2:C$10,"<"&ROW($A$35000:$A$60000),$A$2:$A$10,I1)>0))

I also limited the number of rows to iterate. The formula will iterate from 28-Oct-1995 to 8-Apr-2064. If those dates are not sufficient then please change the row numbers to match the dates desired.

Using the full column will cause a slow down as each formula will iterate over 2 million times. these will only iterate a mere 50,000 time for each formula.

enter image description here

EDIT

As explained above we want to limit the iterations. This formula will automatically limit the iterations to the MIN and MAX dates.

=SUMPRODUCT(--(COUNTIFS(B$2:B$10,"<"&ROW(INDEX(A:A,MIN(B:C)-1):INDEX(A:A,MAX(B:C)+1)),$A$2:$A$10,I1)-COUNTIFS(C$2:C$10,"<"&ROW(INDEX(A:A,MIN(B:C)-1):INDEX(A:A,MAX(B:C)+1)),$A$2:$A$10,I1)>0))

Upvotes: 1

Related Questions