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

  • Ryan Kramer
    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
    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
    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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!