Rolling Total

How would one take a table with names and hours, then calculate a rolling 30 day total of hours based on name? The Table is extremely dynamic and there are multiple entries of the same name.

Example:

Name Date hours

james x 5
john x 6
lisa x 10
james x 6
john x 10
james x 3

Assuming all dates for this example table are in the last 30 days, james' rolling total would be 14hrs; then if the last entry (3) for james fell outside 30 days, it would then change to 11 hrs

Pls attach a sample worksheet with actual date, not "x"
See "how to attach" on the top of page, yellow banner.

Here is your formula.

=SUMIFS(Table1[Hours],Table1[Name],[@Name],Table1[Date (DD-MM-YY)],"<="&$C$16,Table1[Date (DD-MM-YY)],">="&$C$16-30)

Had some issues with your table however. There were additional spaces in some of the Jack's so they did not match up.

Larry and Jim were not always capitalized so they did not match up.

C16 needed to be shown as a date and not text.

This formula works when those items are applied.

Attention to details are important in Excel otherwise you do not get the right answers.

Here is the formula I've adapted:

=SUMIFS(Table3[Down Time
(x.x Hours)],Table3[Machine Name],[@[Machine Name]],Table3[Date (DD-MM-YY)],"<="&$I$6,Table3[Date (DD-MM-YY)],">="&$I$6-30)

For the first name, it picks up only one entry and for the rest it reports 0

Ive double checked name format

I also addressed the date (NOW)

Additionally id like to now have to dedicate a cell to read todays date from

Have you made sure that the names in each location are exactly the same. No spaces before or after the name or capitalization? Now includes the time, so I would try with Today() which is the date and no time.

Now it just doesnt work, I will keep troubleshooting it

and is there anyway to not have to have a dedicated cell for time?

WAIT! Fir some reason if i change the date to not todays date for entry it works!!
how do I account for this? I was a total hours for today + the last 29?

If you can't use Today() then to put the current date in the cell required you can use this keyboard function Ctl + ; that is hold down the Control Key and the semi-colon key at the same time to generate hardcoded today's date.

Alternatively, you could write a VBA script to have the cell populated with Today's date upon opening of the workbook.

Here is an example

Please Login or Register  to view this content.

Change the range you wish to populate.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.