Solved Adding running total daily, per week, then reset.

March 10, 2016 at 07:13:46
Specs: Windows 7
 I have a spreadsheet that has 6 columns. Of those columns I have DATE, Daily total, and Weekly Total. There can be several entries for the same day. I need to have a running total for the DAILY and weekly. At the end of the week, I would like it to reset to 0 or to the next Day/week totals.

See More: Adding running total daily, per week, then reset.

March 10, 2016 at 12:14:35
 Here is a modified version of the Total sheet, just in case you can't rememberwhat Dates go with which weeks:``` A B C D E F 1) 2016 2) Week Nmbr Start Week End Week Customer1 Customer2 Customer3 3) 1 Monday, December 28, 2015 Sunday, January 03, 2016 0:00 0:00 0:00 4) 2 Monday, January 04, 2016 Sunday, January 10, 2016 0:00 0:00 0:00 5) 3 Monday, January 11, 2016 Sunday, January 17, 2016 0:00 0:00 0:00 6) 4 Monday, January 18, 2016 Sunday, January 24, 2016 0:00 0:00 0:00 7) 5 Monday, January 25, 2016 Sunday, January 31, 2016 0:00 0:00 0:00 8) 6 Monday, February 01, 2016 Sunday, February 07, 2016 1:15 1:05 0:00 9) 7 Monday, February 08, 2016 Sunday, February 14, 2016 0:00 0:00 0:00 10) 8 Monday, February 15, 2016 Sunday, February 21, 2016 0:00 0:00 0:00 ```In Cell B3 enter the formula:=DATE(\$A\$1,1,1) - WEEKDAY(DATE(\$A\$1,1,1),2) + (A3-1)*7 + 1Drag downIn Cell C3 enter the formula:=DATE(\$A\$1,1,1)- WEEKDAY(DATE(\$A\$1,1,1),2) + A3*7Drag down EDIT ADDED:Forgot to mention, you'll need to modify the formula in Cell D3=SUMIFS(Sheet1!\$E\$2:\$E\$4,Sheet1!\$A\$2:\$A\$4,D\$2,Sheet1!\$G\$2:\$G\$4,\$A3)Again, drag down, then drag across to get the Totals for each Customer by Week Nmbr.MIKEmessage edited by mmcconaghy

#1
March 10, 2016 at 08:12:16
 One possibly method for getting the Weekly value is to use a Helper Column.Let's say your Dates are in Column A and the values to SUM are in Column B. In my example, I'll put the Helper Column in Column C. (It can be placed anywhere you like as long as you reference it properly in your formula)Assuming your Dates start in A2, enter this in C2 and drag it down as far as you need.=WEEKNUM(A2)Now, for your Weekly SUM, you can use:=SUMIF(C:C,"="&WEEKNUM(TODAY()),B:B)This will SUM all values in Column B where TODAY's Week Number matches the Week Number in Column C. Once the actual Date (i.e. the date returned by system's calendar via the TODAY function) moves into the next week, the WEEKNUM for TODAY will automatically increment and the formula will only SUM values with the same Week Number.I'm not quite sure what you are looking for as far as the "daily" total. Are you looking for 7 cells, each of which shows the SUM of the values for each individual day of the current week?message edited by DerbyDad03

Report •

#2
March 10, 2016 at 08:23:17
 Not sure how your sheet is actually set up, but using any of the =SUM() functionsshould get you what your looking for.If your data looks like:``` A B C 1) Date Daily Weekly 2) Monday, March 07, 2016 10.005 3) Tuesday, March 08, 2016 15.568 4) Wednesday, March 09, 2016 13.875 5) Thursday, March 10, 2016 12.01 6) Thursday, March 10, 2016 8.092 7) Friday, March 11, 2016 ```For a Daily total, you could do something like:=SUMIF(A2:A7,TODAY(),B2:B7)This will SUM only those figures that correspond withTODAY'S date, Thursday, March 10, 2016.So you should get a total of 20.102.Tomorrow, the figure will be gone, and you start a new daily total.But where are going to put the Daily and Weekly total figures?At the bottom of the columns, or somewhere else?Or does your work sheet not look like the example above?MIKEhttp://www.skeptic.com/

Report •

#3
March 10, 2016 at 08:25:03
 I should have mentioned it's for keeping track of service calls. There may be more than one service call on that day. Some days are longer than others and wanted a running total of hours for the day/week.7 day work week.

Report •

Related Solutions

#4
March 10, 2016 at 08:31:06
 Close!My data is as follows:Customer.....DATE.....Start....End....DAILYHRS....WEEKLYHRSCustomer1.....2/3/16....01:00....02:00....0:01:00........0:01:00Customer2.....2/3/16....02:30....03:35....0:01:05........0:02:05Customer1.....2/4/16....01:30....01:45....0:00:15........0:02:20 message edited by DavidDevo

Report •

#5
March 10, 2016 at 11:33:08
 OK, so now we need to SUM not only by Week Number but also by Customer.The simplest way, is to do your totaling on a new sheet.So with sheet 1 looking like:``` A B C D E F 1) Customer DATE Start End DAILYHRS Wk Nmbr 2) Customer1 February 03, 2016 1:00:00 2:00:00 1:00:00 6 3) Customer2 February 03, 2016 2:30:00 3:35:00 1:05:00 6 4) Customer1 February 04, 2016 1:30:00 1:45:00 0:15:00 6 ```Note, we removed the Weekly Hours in Column F and replaced it withthe Week Number, using DerbyDad03s suggestion.Now create a new sheet that looks like:``` A B C D 1) 2016 2) Week Nmbr Customer1 Customer2 Customer3 3) 1 0:00 0:00 0:00 4) 2 0:00 0:00 0:00 5) 3 0:00 0:00 0:00 6) 4 0:00 0:00 0:00 7) 5 0:00 0:00 0:00 8) 6 1:15 1:05 0:00 9) 7 0:00 0:00 0:00 10) 8 0:00 0:00 0:00 ```In Cell B3 enter the formula:=SUMIFS(Sheet1!\$E\$2:\$E\$4,Sheet1!\$A\$2:\$A\$4,B\$2,Sheet1!\$G\$2:\$G\$4,\$A3)Drag down as many rows as needed,Drag right as many columns as needed.This will give you the Totals for each Customer by WeekThe formula utilizes the Column Headers in Row 2, IE Customer1, Customer2, etc.as well as the Week Numbers in column A.You will need to take that into consideration if you change the format of the sheet.See how that works for you.MIKEmessage edited by mmcconaghy

Report •

#6
March 10, 2016 at 12:14:35
 Here is a modified version of the Total sheet, just in case you can't rememberwhat Dates go with which weeks:``` A B C D E F 1) 2016 2) Week Nmbr Start Week End Week Customer1 Customer2 Customer3 3) 1 Monday, December 28, 2015 Sunday, January 03, 2016 0:00 0:00 0:00 4) 2 Monday, January 04, 2016 Sunday, January 10, 2016 0:00 0:00 0:00 5) 3 Monday, January 11, 2016 Sunday, January 17, 2016 0:00 0:00 0:00 6) 4 Monday, January 18, 2016 Sunday, January 24, 2016 0:00 0:00 0:00 7) 5 Monday, January 25, 2016 Sunday, January 31, 2016 0:00 0:00 0:00 8) 6 Monday, February 01, 2016 Sunday, February 07, 2016 1:15 1:05 0:00 9) 7 Monday, February 08, 2016 Sunday, February 14, 2016 0:00 0:00 0:00 10) 8 Monday, February 15, 2016 Sunday, February 21, 2016 0:00 0:00 0:00 ```In Cell B3 enter the formula:=DATE(\$A\$1,1,1) - WEEKDAY(DATE(\$A\$1,1,1),2) + (A3-1)*7 + 1Drag downIn Cell C3 enter the formula:=DATE(\$A\$1,1,1)- WEEKDAY(DATE(\$A\$1,1,1),2) + A3*7Drag down EDIT ADDED:Forgot to mention, you'll need to modify the formula in Cell D3=SUMIFS(Sheet1!\$E\$2:\$E\$4,Sheet1!\$A\$2:\$A\$4,D\$2,Sheet1!\$G\$2:\$G\$4,\$A3)Again, drag down, then drag across to get the Totals for each Customer by Week Nmbr.MIKEmessage edited by mmcconaghy