Forum Discussion
Solved
Sum from cell across multiple sheet, depending on hire date
I can not figure this out, I am trying to make a summary sheet that will count the vacation days across multiple sheets from "Start" to "All" (these are timesheets between those named sheets) for emp...
I have been testing the formula and it all works great except that it doesn't take into account the timesheet date in M4, it seems to be counting past the anniversary date.
For example, if I date the timesheet 10/1/24 in cell M4, Employee 1 still has a vacation time counted when it should have restarted on 11/11/24.
I think I figured out a way to have it work (attached). It's a roundabout way but seems to work.
May consider below:
- Aggregate Vacation Days Across Sheets: You’ll need a formula that pulls the vacation days from multiple sheets.
- Check Anniversary Reset Condition: Identify when the hire date anniversary occurs and reset the count accordingly.
Sample for your reference:
=SUMIFS(INDIRECT("'" & A1:A10 & "'!F5"), INDIRECT("'" & A1:A10 & "'!A5"), ">= " & DATE(YEAR(TODAY()), MONTH(B5), DAY(B5)))