Sum data and not change data after certain date.

BethanyB

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.

ericncarr

@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.

Brent C. Wilson

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?

BethanyB

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.

KPH

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.

BethanyB

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.

KPH

Good luck!