Sum data and not change data after certain date.
I am trying to sum data as of XX/XX/XXXX of each week. I want this data then to go into a cell and not be changed the next week. For example, on 1/15/24, a sum formula was used to calculate the number hours remaining was 3891. On 1/22/24, the sum formula was used to calculate the number of hours remaining as 3201. Is there a formula that will allow the 3891 to stay static on for 1/15/24 and not change without touching the sheet? Then on 02/19/24, I want the sum formula to run and pick up the number and remain static.
Answers

@BethanyB it's a little hard to say without knowing how you are summing the data but you'd basically want to put a limiter on your Sumif(s) formula based on the date in your date column. I would assume the data you have has some date value attached to it so your formula would be something like:
=sumifs(range1, criteria1, {Date Range}, < = Date@row)
As long as your underlying data doesn't change then this will remain static.

I think I would do this by having a helper start_date and end_date column.. for the week row (ex Start_Date: 15Jan End_Date 21 Jan). Then you could use a summary
=SUMIFS([Hours Column]:[Hours Column], [Date Column]:[Date Column], >=Start_Date@row, [Date Column]:[Date Column], <=End_Date@row)
This should only sum the values in the hours column whose dates fall within the row's Start_Date and End_date ranges.
This is always calculating, so if someone went back in time and added an entry, it would change.Does that work?

There is no date column in the source sheet. On the source sheet, the remaining hours changes based on a status that changes throughout the week. Then the support sheet sums all "remaining hours" column.

Could you set up an automation to copy the row that contains the sum to another sheet once a week? That other sheet would remain as the static record.

KPH, I think that solution may work best. I was hoping to have only a formula to pull in data.
Thank you all for the suggestions.

Good luck!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!