Results 1 to 5 of 5

Thread: Countif formula for emp scheduling worksheet

  1. #1

    Countif formula for emp scheduling worksheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    im trying to figure out how to write out the proper countif

    The formula is needed in the Lines tab B8:B38. The range is in the schedule tab B8:B33 for in time. C8:C33 out time.

    In the schedule tab I have Cashier's schedules I want to be able to have their schedules tally in the Lines tab. So if there is a cashier working 8-4:30 I want there to be a tally in the lines sheet from 8:30AM to 4:45pm (Always stops 15 minutes before the end of their shift in this case stop at 4:30). That would continue for every time listed.

    I have this formula in there and it seems to work but I know it's very basic to that first time, not sure what you would do for the next increment of time since it will need to include the previous time as long as the shift didn't end.
    =COUNTIF(Schedule!B8:B33,"8:30")


    And how do I get it to fill all the way down stopping at 4:30 for the 8a-5p example I used above.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    First, please change the times in column A of Lines sheet to actual times (not numbers).

    Then perhaps you mean?

    =COUNTIFS(Schedule!$B$8:$B$33,"<="&$A8,Schedule!$C$8:$C$33,">="&$A8)

    copied down.


  3. #3
    Quote Originally Posted by NBVC View Post
    First, please change the times in column A of Lines sheet to actual times (not numbers).

    Then perhaps you mean?

    =COUNTIFS(Schedule!$B$8:$B$33,"<="&$A8,Schedule!$C$8:$C$33,">="&$A8)

    copied down.
    Okay I changed those times in Column A. That looks good the only thing is I need it to stop either 15 or 30 minutes prior to end of shift. So if shift ends at 5:00 then it should stop counting at 4:30. What does that & sign mean after the <=

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =COUNTIFS(Schedule!$B$8:$B$33,"<="&$A8,Schedule!$C$8:$C$33,">="&$A8+TIME(0,30,0))

    The & means "and". it appends the value in cell A8 to the >= operator. This is the syntax that SUMIF(S), AVERAGEIF(S) and COUNTIF(S) uses to reference cell values in the criteria.


  5. #5
    Quote Originally Posted by NBVC View Post
    Try:

    =COUNTIFS(Schedule!$B$8:$B$33,"<="&$A8,Schedule!$C$8:$C$33,">="&$A8+TIME(0,30,0))

    The & means "and". it appends the value in cell A8 to the >= operator. This is the syntax that SUMIF(S), AVERAGEIF(S) and COUNTIF(S) uses to reference cell values in the criteria.
    Thanks for the explanation!! Everything is working perfectly.

Posting Permissions

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