total and subtotal time

Copper Contributor

I have the need to calculate the accumulating time and then the subtotal when a value in another column changes. My example would be Column A is date/time; Column B contains one of the following "Start", "Update" and "Stop".  I need to sum the time if Column B is "Start" and "Update" and subtotal when it is "Stop" and then start the sum again on the next "Start".  Also if I get two "Start"s in a row flag the row of data and either continue the suming or give the user the choice to continue suming or force a subtotal or at least check to see if there was a missing "Stop".

2 Replies

@jspry , for the model like this

image.png

cumulative time could be calculated as

=IF(($B2="Start")*NOT(   (COUNTIF($B1:B$2,"Stop")<>COUNTIF($B1:B$2,"Start"))*(COUNTIF($B1:B$2,"Start")>1)*($B2="Start")  ),0,$A2-N($A1)+N($C1))

and Start without previous Stop highlighted with conditional formatting rule with formula

=(COUNTIF($B1:B$2,"Stop")<>COUNTIF($B1:B$2,"Start"))*(COUNTIF($B1:B$2,"Start")>1)*($B2="Start")

applied to the column B

Given that you used many or’s in your explanation, please attach your sample file with desired results so that the logic thereof could be deciphered.