Insert subtotal with more than one change

  • This problem has me a bit stumped. I would like to subtotal the calculated elapsed time for each change in “new Shift” in the Shift column, but the subtotal must also include the “same” rows beneath the “new Shift” designation until it reaches the next “new Shift”. At that point, a new subtotal would be needed for the next “new Shift” and its subsequent “same” rows. In other words, each time a new shift starts, the calculated elapsed time needs to sum anew. New shift indicates a new shift has started. “Same” indicates it belongs to the “new shift” previously designated above it. This subtotal would have to iterate many more times down the spreadsheet than is shown in the given example data. This is only a subset of the data.

    If I try to use the Excel subtotal feature, what happens is that it subtotals on “new Shift” and subtotals on “Same” rows separately. I need it subtotal the first instance of “new Shift” and all the “Same” rows beneath it until it gets to the next “new Shift”.

    I also tried a pivot table but seem to run into similar problem.

    In the attached workbook, I give a sample of the raw data, and in a separate sheet a sample of what the desired results might look like.

    Any guidance would be greatly appreciated.

    Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!