yogi_Compute Sum Of Weekly Hours Every Monday

Google Spreadsheet   Post  #2090

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-03-2016

question by: Ramsey Persing:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!msg/docs/UYB5AZCB-gU/MkedvMdEBgAJ

How to Create a Variable SUM Range Formula

 I'd love some help with a Google Sheet Formula if anyone has any ideas. I make my own hours and I created a simple Google Form to help me track my hours throughout the week. It only asks for the weekday and hours worked that day. The form filters all my answers to a spreadsheet. I'm trying to make a formula that automatically calculates the hours worked for that current week. It looks for the most recent occurrence "Monday", then sums all the hours after that. 

I created a sample sheet  to look at HERE for the data. 

I worked out this to return the address of the cell next to Monday that logs the hours worked that day, but then it won't work when I put that in a SUM formula. Ideas?

=cell("address",index(B4:C11,match("Monday",C4:C11,0),1))

-As a side note, this doesn't search the whole range either. It's only the last 8 entries. That way I don't have two occurrences of Monday. If there's a way to pull only the most recent week that would be nice too. Nor can it just be the last 5 days because some days I work 4 hours in the morning and a more in the evening as in my sample sheet. It's nice to enter my hours right after I work so I don't forget. The only day I couldn't do this is Monday if it's searching for the most recent occurrence.