# Count the total number of shifts worked

1. ## 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.

2. ## Re: Count the total number of shifts worked

whats the answer meant to be ? 16?

=COUNTIFS('Calculation Data'!C:C,A3,'Calculation Data'!F:F,"<"&F3)

3. ## Re: Count the total number of shifts worked

Hi and welcome to the forum

I will take a look at your file and see what I can come up with.

Just some info vlookup() will return ONLY the very 1st instance of something that you are searching for, it cannot add or count anything (generally)
Countif() and countifS() do just that - they count stuff based on certain specified criteria...countifS() can take multiple criteria, countif() only 1
sumif() and sumifS() add stuff based on certain specified criteria...sumifS() can take multiple criteria, sumif() only 1

Edit: humdingaling looks like they may have what you want?

4. ## Re: Count the total number of shifts worked

ok found some issues with the countifs, the 2nd criteria does nothing in current state....date comparison by line is not good this way

so in calculation data sheet i added need column with formula
=IF(F2>D2,"Y","N")
to make sure entitlement is warranted

then back on summary page countif formula now becomes
=COUNTIFS('Calculation Data'!C:C,A3,'Calculation Data'!G:G,"Y")

ive amended the above in the sheet
let me know if it does what you want it to

5. ## Re: Count the total number of shifts worked

Now why didnt i think of that, it seems so simple when you put it like that.

Thanks heaps for your help with this Humdingaling, definitely makes it easier now.

Also thanks FDibbins for your help with the different formula's

There are currently 1 users browsing this thread. (0 members and 1 guests)