SUMIF from a date plus 7 days back
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
Answers
-
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
-
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.
-
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.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!