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: 15-Jan 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!