SUMIF from a date plus 7 days back

Sheryl Roland

I'm trying to total the current week in one field and the previous week in a second field. The reference sheet lists every date of the year in one column and the amount of transports for each date in another column, along with a lot of other information. On the summary sheet, I have one row with a column for the date of the current week and another column for the total transports for that week. The next row is for the previous week. Currently, I am typing the dates in the formula, how can I total 7 days back from the date that I enter on the summary sheet. I want to reference the date cell. The date cell on the summary sheet is changed each week on Monday.

Reference Sheet -Total Transports by Payment Class

Summary Sheet (P Tx range is the P column and PIMCF Date is the Date column from the reference sheet)

I've tried SUMIFS and the above SUMIF formula and I am at a standstill. I know somewhere out there has the knowledge!!!

Thanks,

Sheryl

Ryan Kramer

@sherylslife,

If I understand correctly, you should be able to specify a reference cell and then in that cell specify the way you want to set the date value. For instance today would be TODAY(), 7 days ago =TODAY(-7) and 7 days in the future =TODAY(7).

This will get you away from having to specify the date manually.

Ryan

Sheryl Roland

The current week and previous week field on the summary sheet should remain the same until I change it the next week. If I use the TODAY() function, the total or date field change every day. I want to be able to sum the previous 7 days from the date in the date field. I made a workaround by adding a column on the reference sheet that I manually summed the previous 7 days for every day on the sheet. So far it will do, but it was a lot of leg work.

Sheryl Roland

Maybe I should point out that the data is displayed on a Weekly Dashboard giving the totals for the current week meaning Monday-Sunday and the previous week meaning two weeks ago Monday-Sunday. I only want the dashboard to change on Monday which I have to do manually.

rgochee

You may want to try to use the Week of the Year as your Reference -

Add another Column Next to the Date Column in your Reference Sheet and use this formula =Weeknumber(Date@row)

Change your Reference on your Summary Sheet " {PIMCF] " to the new Column "Week" added on the Reference Sheet

Change your formula on the Summary Sheet =sumifs({P Tx},{PIMCF},=Weeknumber(Week@row))

Let me know if this helps