Running total of hours based on last 7 days

[Guest]

I am trying to work up an excel sheet that will allow me to calculate a
running total of hours for the past 7 days based on the hours inputted for
each day, but allowing for a "reset to "0" running total if the word "reset"
is entered into a column.

a) I have column "a" designated as "date"

b) I have column "b" designated as "reset"....this the part that is causing
me grief

c) I have column "c" designated as "daily hours"

d) I have column "d" designated as "recap past 7 days"


Column "a" has the date listed as far ahead as needed.....

Column "b" is empty until the word "reset" is entered......

Column "c" is filled in daily with the total number of hours that the
employee worked for that date

Column "d" totals the accumulated hours for that day and the preceding 6 days


When "reset" is entered into column "b" beside the appropriate date, the
cell in column "d" that would have normally totalled that day's hours and the
preceding 6 days reverts to "0".

I am fine up to this point......my quandary is that the following cell(s) in
column "d" is(are) still totalling the hours for it's date AND the preceding
6 days......

I am trying to resolve some way of having the cells in column "d".......not
total the hours back past a "reset" date......

Thanks in advance for any thoughts/consideration

 

[Don Guillett]

It appears that a sumproduct formula would work for you.Try this in d2 and
copied down

=SUMPRODUCT(($A$2:A6>A5-7)*($A$2:A6<A5)*$C$2:C6)