# Thread: Countif formula for emp scheduling worksheet

1. ## Countif formula for emp scheduling worksheet

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.

2. 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. Originally Posted by NBVC
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. 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. Originally Posted by NBVC
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
•