Selected Answer
Enter the formula below in cell D17 of your example and copy down.
=COUNTIFS(INDEX(SignIn,,1),$B17,INDEX(SignIn,,2),">="&IF($C17="day",0.25,0),INDEX(SignIn,,2),"<"&IF($C17="day",0.75,0.25)) + IF( $C17="night",COUNTIFS(INDEX(SignIn,,1),$B17,INDEX(SignIn,,2),">=0.75",INDEX(SignIn,,2),"<=1"),0)
The formula refers to the named range "SignIn" which is C5:D10 in your example, INDEX(SignIn,,1) specfies the range's first column, INDEX(SignIn,,2) specifies the second. These expressions can be replaced in the formula with $C$5:$C$10 and $D$5:$D$10 respectively. I think that the named range makes the formula easier to read but that may be a matter of taste. So, suit yourself. Bear in mind, however, that a named range can be made dynamic, meaning it can adjust automatically when the user adds or deletes rows. To achieve that behaviour without a named range takes a lot of functions which would render the above formula unmanagable.
To quickly assign a name to a range, type the desired name in the Name Bar, and press Enter. The name bar is the field above column A, to the left of the Formula Bar, where you normally see the address of the active cell.. After a range is named that name will appear in the Name Bar when selected. The Name Bar also has a drop-down list in which all named ranges on the tab are listed, and you can select one by clicking the name. Use the Name Manager, assible from the Ribbon's Formulas tab to modify an existing named range.