I need help adjusting the formulas in the 2nd picture attachment (Evaluate.jpg) so that they adjust when the data it is pulled from is filtered (by Role, Manager, etc.). I have figured out how to have the "rostered" calculations to update using =SUBTOTAL(2,Data[(Mon) START]) but I need help with "Present" (shows the total number of employees working at the times to the left), "Starting" & "Leaving" (shows the number of employees starting or leaving at the times to the left).
Right now, the formulas are:
Right now, the formulas are:
- Present: =SUMPRODUCT(((Data[(Mon) START]<=A4)*(Data[(Mon) END]>=A4)+((Data[(Mon) START]<=A4)+(Data[(Mon) END]>=A4))*(Data[(Mon) END]<Data[(Mon) START]))*(Data[(Mon) START]<>""))
- Starting: =COUNTIF(Data[(Mon) START],A4)
- Leaving: =COUNTIF(Data[(Mon) END],A4)