Forum Discussion

LisaB1009's avatar
LisaB1009
Copper Contributor
May 16, 2025
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...
excel
Formulas and Functions
  • LisaB1009's avatar
    LisaB1009
    May 28, 2025

    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.

     

     

     

SP247's avatar
SP247
Copper Contributor
May 19, 2025

Hi,

Below is a possible solution as per my understanding.

Formula on C3 to C5 ...

On C3

=COUNTIFS(INDIRECT("'" & A3 & "'" & "!A2:A5"),">=" & DATE(YEAR(B3)+$B$1-1,MONTH(B3),DAY(B3)), INDIRECT("'" & A3 & "'" & "!A2:A5"), "<" & DATE(YEAR(B3)+$B$1,MONTH(B3),DAY(B3)))

 

On C4

=COUNTIFS(INDIRECT("'" & A4 & "'" & "!A2:A5"),">=" & DATE(YEAR(B4)+$B$1-1,MONTH(B4),DAY(B4)), INDIRECT("'" & A4 & "'" & "!A2:A5"), "<" & DATE(YEAR(B4)+$B$1,MONTH(B4),DAY(B4)))

 

Here is the explanation

Requirement - count to reset on their hire anniversary month and day.

B1 (user input) - is the Calander year to be consider for each employee from the hired date.

For example,

For Employee 1, 1 in B1 is for date 01/02/2001 to 31/01/2002 & 2 for 01/03/2002 to 31/01/2003.

This will be applicable for all remaining as per their hired date.

This can be changed by adding years in columns. 

Hope this may help.

 

Resources