Sorry for the delay. Okay I'm not mister formula and if there is one that can work for you it's going to be tricky and I'm sure long. What I did was I wrote a custom function. I've tested it a little, but you should check it out and see how it works for you.
I would suggest you make a copy sheet to test on.
You workbook needs to be in a .xlsm format as this function is a macro and you need to enable macros for the workbook.
Copy the code below, choose Control + F11 in you workbook this will take you into the visual basic editor. Go to the insert menu and choose "Module", when you insert the module an empty white window will come up. Paste the code there and just close this window to go back to Excel. The function is very similar to any other function you use like Sum or Average etc.
The best way to use it is highlight a cell where the formula will be and click the "fx" button up above the column letters if you have them turned on to the left of the formula editor where you see the text you are typing into a cell. A box will pop up that says insert function at the top. There's a drop down box that says "Or select a category:" choose this drop down box and scroll down to User Defined. Click on the function that says "CalculateTicketHours", should be the only one there and choose ok. The box that pops up is one of the type where you click on the box and then choose the cell. As you can see on the left is a clue as to what cell needs to go in each box. Make sure you put in the pending cells also even if they are empty and or you know they won't be used. If you don't it will cause an error. If you don't have pending cells then type a zero into each one of those cells and it'll work fine. The reason I want you to go to this box is that when you input a built in Excel function like = Sum as you are typing it there's a little window that pops up that give you clues as to what to put where, but with a user defined function you don't get that, but if you open the insert function box I've given you the clues you need. Once you get used to it and you know what needs to go where and in what order you can just type =CalculateTicketHours( and put the cells in) hit enter and you are good to go.