Sum from cell across multiple sheet, depending on hire date

Let's try this. If indeed your actual database ends up holding weekly records that are more than a year old, then you will need to handle that by another condition. I've changed the primary function to an IFS function--which allows for multiple conditions and consequences.

See the attached.

=================

By the way, I use the value 360 to reject any data from a sheet that is more than 360 days older than TODAY(). You might prefer to make that 365. Test it with some sheets that carry week-ending dates on the boundary line of "too old." It wouldn't surprise me if there are some cutoff dates that are boundary-line ambiguous for one week. That is to say, any single week's summary might count days it shouldn't, or vice versa. I have not done any exhaustive testing of the many combinations of dates that are conceivable. You could play around a bit more with anniversary dates that are exactly one year before the date TODAY() to see what happens. It might be that one or more of the comparatives should be changed to "=>" or "<=" or the like. But that also depends on how absolutely precise the results need to be on any one day's summary. After all, the value of TODAY() will be different tomorrow, which means that those occasional ambiguities will resolve themselves.

================

A final note: This clunk formula could be made more elegant, using LET to define each calculation once, but for now, this clunky one allows you to read it (I hope) more clearly. And it could be even more elegant if you used LAMBDA to define your own function using the three dates as inputs. Those are some learning opportunities for you.