Summarising data based on the first instance of an indicator

[slamanager]

HI All,

I have data for tickets which have multiple steps. I have this data sorted and summarising the entire ticket duration but ive been informed when a ticket has a ceratin step that is the point "management" need the value calculated at.

example:
Col A - ticket id
Col B - ticket status (Working, re working, pending A, pending B, Man Chk, Man Re work, Final etc....)
Col C - Order number ( if there are 10 steps in the ticket each line has its step number e.g 1 2 3 ....to 10)
Col D - Duration in minutes of each step

tickets can be 5 to 50 rows each and have multiple "Final" instances.

Now I can locate the first and all instances of "Final" and display their step number I need a formula in E that when for each ticket in the data set (currently approx 250 over 5000 rows) when it shows the first instance of "Final" how do i calc the sum from Step 1 to Step ?? and display the total.

I think this could be a SUM & OFFSET combination but i think this setup has more combinations.

Thanks.

 

[pbornemeier]

If you have a formula that shows the step that contains the first "final" for a ticket, use it to replace 6 in this column E formula:
=SUMPRODUCT(--(A:A=A2),D:D,--(C:C<6))