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.

✔ 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:00In 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

message edited by mmcconaghy

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

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, 2016For 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

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.

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

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 6Note, 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:00In 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

message edited by mmcconaghy

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:00In 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

message edited by mmcconaghy

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History