Calculate # of employees each hour; sumif formulas

[sprdsheet]

Hi.

I'm new to this forum. I have read many posts regarding this sort of thing using different functions of Excel.

I have a working formula, though it outputs the wrong times for 3 specific hours. Otherwise, the other time periods are fine.

I have a worksheet using 2 columns of employees. Columns E (time in) and F (time out) are the full time associates. Colums O (time in) and P (time out) are the part time associates.

Page 2 of the spreadsheet is where my output columns are.

times are as follows:

5:00 (b77) 5:59 (c77) 10:00 (f77) 10:59 (g77) 15:00 (L77) 15:59 (M77) 20:00 (P77) 20:59 (Q77)
6:00 (b78) 6:59 (c78) 11:00 (f78) 11:59 (g78) 16:00 (L78) 16:59 (M78) 21:00 (P78) 21:59 (Q78)
7:00 (b79) 7:59 (c79) 12:00 (f79) 12:59 (g79) 17:00 (L79) 17:59 (M79) 22:00 (P79) 22:59 (Q79)
8:00 (b80) 8:59 (c80) 13:00 (f80) 13:59 (g80) 18:00 (L80) 18:59 (M80) 23:00 (P80) 23:59 (Q80)
9:00 (b81) 9:59 (c81) 14:00 (f81) 14:59 (g81) 19:00 (L81) 19:59 (M81)

The formula I have currently:

(IF(F80>=E7:E47,IF(G80<F7:F47,1,0),0)),(IF(F80>=O7:O47,IF(G80<P7:P47,1,0),0))

The numbers that malfunction are 13:00, 14:00, and 15:00. All other hours output correctly.

If I use the even hour (13:00) etc... It outputs correctly. But, if the associate comes in at any other time greater than 13:00, like 13:30...it does not work for the 3 hour period mentioned above. My test has 2 at 5am, 6am, 7am, 8am, 9am, 10am, 11am, 12pm, 13:30, 14:30, and 15:30. The times output the following: 2, 4, 6, 8, 10, 12, 14, 16, 14, 14, 14, 14, 14, 12, 10, 8, 6, 4, 2, 0.

Any help would be greatly appreciated.

[Saarang84]

Post a sample workbook with data (insensitive data) to work on your requirement further.

[sprdsheet]

TEST3.xlsmGood Morning, Sarang. Thank you for your response. Attached is what i'm looking at.

Thank you.

[sprdsheet]

Maybe this time will work.

[sprdsheet]

So the test sheet is attached below. Any advice on how to correct the function of this formula?

(IF(B77>=E7:E47,IF(C77<F7:F47,1,0),0)),(IF(B77>=O7:O47,IF(C77<P7:P47,1,0),0))

As mentioned before, everything calculates correctly except for 13:00-13:59, 14:00-14:59, and 15:00-15:59. However, if I have the employee coming in at 13:00, 14:00, or 15:00 instead of 13:30, 14:30, and 15:30 everything calculates perfect.

Thanks again.