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.

Report •

✔ Best Answer
March 10, 2016 at 12:14:35
Here is a modified version of the Total sheet, just in case you can't remember
what 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 + 1

Drag down

In Cell C3 enter the formula:

=DATE($A$1,1,1)- WEEKDAY(DATE($A$1,1,1),2) + A3*7

Drag 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.

MIKE

http://www.skeptic.com/

message 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?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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() functions
should 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 with
TODAY'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?

MIKE

http://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....WEEKLYHRS
Customer1.....2/3/16....01:00....02:00....0:01:00........0:01:00
Customer2.....2/3/16....02:30....03:35....0:01:05........0:02:05
Customer1.....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 with
the 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 Week

The 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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
March 10, 2016 at 12:14:35
✔ Best Answer
Here is a modified version of the Total sheet, just in case you can't remember
what 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 + 1

Drag down

In Cell C3 enter the formula:

=DATE($A$1,1,1)- WEEKDAY(DATE($A$1,1,1),2) + A3*7

Drag 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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Ask Question