Count the total number of shifts worked

12 - BNEMH Add AL Jun 13v2.xlsxHi All,


Im new to the forum, i just joined as i seek assistance with a formula that I'm stuck on, now i'm not sure it is even possible but it cant hurt to ask.

I have a spreadsheet with multiple worksheet. I wish to write a formula that looks up "Emp code" on one work sheet, then counts the total number of shifts worked for that employee on the second work sheet providing it does not occur before their entitlement start date.

This is the formula i tried but it only returns a value of 1, which is not correct.

Worsheet = Summary & Cell G3
=VLOOKUP($A3,'Calculation Data'!$C$2:$G$316,5,COUNTIF('Calculation Data'!G2:G203,'Calculation Data'!F2>'Calculation Data'!D2))


Ive attached the speadsheet so anyone can have a look, i want the total number of shifts worked after a entitlement start date to be returned in column G on Summary worksheet, using either the information on Pivot Table or Calculation Data worksheet.

Currently the worksheet does not have any dates for any employee prior too their entitlement start date as i manually go in an remove these dates, but this is not only time consuming it can all lead to errors.

If this doesnt make sense please let me know and ill try and explain it in further detail.

Thanks in advance for any assistance with this.