Countif formula for emp scheduling worksheet

beh162

beh162

New member
Joined
Jan 15, 2015
Messages
7
Reaction score
0
Points
0
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.
 

Attachments

  • Excel sheet copy FORUM.xlsx
    24.1 KB · Views: 24
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.
 
NBVC said:
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.
Click to expand...

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 <=
 
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.
 
NBVC said:
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.
Click to expand...

Thanks for the explanation!! Everything is working perfectly.
 
You must log in or register to reply here.
Back
Top