Counting hours since last day off

[skizgg]

Hi there. I'm currently working a job that allows me to take a day off when I reach a certain amount of hours worked. I'm creating a spreadsheet to help track this. Is there any formula that I could use to track the amount of hours I have worked since my last day off? For instance, I have a column that has a cell for each day showing how many hours i worked that day. When i take a day off i will fill that days cell with the word "DAY OFF", I want the function to return the total hours worked since the last "DAY OFF". Any help would be great, thanks in advance!

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[skizgg]

I have attached a Sample Workbook here

[AliGW]

Try this:

=SUMIF(B:B,">"&INDEX(B:B,LOOKUP(2,1/(D:D="DAY OFF"),ROW(D:D))),D:D)

[kvsrinivasamurthy]

Try this

=SUM(INDEX($D$1:$D$50,AGGREGATE(14,6,ROW($D$1:$D$50)*($D$1:$D$50="DAY OFF"),1)+1):$D$50)

[skizgg]

That works great. Thank you very much for your help! I would have never come up with that on my own

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.