+ Reply to Thread
Results 1 to 5 of 5

Count the total number of shifts worked

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Unhappy 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.
    Last edited by Schoeii; 07-02-2013 at 11:34 PM.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    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)
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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?
    Last edited by FDibbins; 07-02-2013 at 11:48 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-02-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1