# Rolling Totals

#### rbuckwalter

##### New Member
This is prbably a fairly simple question, but I'm setting up an attendance spreadsheet for work and want to always be able to keep a rolling 90 day total of hours missed from whatever todays date is.

What would be the simplest way to accomplish this? Data will be in clumn format with date in comun a and hours missed for that dat in column B (or subsequent columns after B for other Staff)

Example: Date in column A and hours missed for that date in Column B. I'm only interested in the hours missed in the last 90 days from whatever todays date is though no matter how far back I have attendance data. Anything over 90 days would drop off the total.

Any help would be appreciated.

Thanks!!
[/img]

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi

You could build a dynamic range and use the sum formula. If you have today's data in the last row of column A (say row 100), then you want to sum the range B11:b100 (or whatever your last column of data happens to be).

You can use the match function to get the row of today's date and use the offset command to provide the sum range.

Along the lines of:
=sum(offset(b1,match(today(),a1:a500,0)-90)ffset(b1,match(today(),a1:a500,0),0))

(note above not tested)
Tony

Hi

Revision to the formula

=SUM(OFFSET(B1,MATCH(TODAY(),A1:A500,0)-90,0):OFFSET(B1,MATCH(TODAY(),A1:A500,0)-1,0))

Tony

I tried using this formula, but I keep getting reference errors. Either #NA or #REF!. Can anyone tell me what I am doing wrong, or specifically which cells this formula is supposed to reference. Thanks a bunch!

Replies
7
Views
81
Replies
6
Views
262
Replies
0
Views
281
Replies
10
Views
626
Replies
0
Views
219

1,203,673
Messages
6,056,675
Members
444,881
Latest member
Stu2407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back