Why does SUMIFS return an incorrect result?

Question

Google Sheets

The table has a Shifts sheet containing data on employee shifts, on the Shifts fill rate by hours sheet I count how many employees are on a shift at a particular hour using this formula (the number of shifts started - the number of completed shifts).

=СЧЁТЕСЛИМН(Shifts!$D$3:$D; "<="&D$1;Shifts!$A$3:$A;$A3)
-СЧЁТЕСЛИМН(Shifts!$E$3:$E; "<="&D$1;Shifts!$A$3:$A;$A3)

In most hours the result is correct, but not always.

For diagnostics, I divided the formula into two separate ones, it can be seen in the 4th line that at 10/13/19 hours, one started shift is missing, while in the rest of the hours everything is correct.

In line 5, similarly, there are not enough completed shifts at 13/19/22 hours.

Answer 1

1. Remove rows for ranges, leaving only fields:
=COUNTIFS(Shifts!$D:$D; "<="&B$1;Shifts!$A:$A;$A2)-COUNTIFS(Shifts!$E:$E ; "<="&B$1;Shifts!$A:$A;$A2)
Because of this, field A2 flew out of your calculation
2. The main problem is the mismatch of time formats in fields D, E of the Shifts sheet and the header on the sheet Shifts fill... Quick solution
: set the header on the Shifts fill... sheet according to all the rules (set the data format to "Time") and copy the time values ​​from it to the Shifts sheet. After you can copy the time to Shifts from previously worked shifts. I copied the time 16:00:00 for 9.12 - it worked as it should.
The normal solution is to make a time directory and take data from it through the drop-down list. And for the header on Shifts fill...,