Error while counting the timestamp

[saravana]

I am getting an error while counting the timestamp between
06:00 to 17:59 and 18:00 to 05:59 (like morning and night shift report)

am using like

got the correct answer
COUNTIFS($A:$A,$E$3,$B:$B,">=06:00",$B:$B,"<=17:59")
COUNTIFS($A:$A,$E$4,$B:$B,">=06:00",$B:$B,"<=17:59")

its showing error
COUNTIFS($A:$A,$E$3,$B:$B,">=18:00",$B:$B,"<=05:59")
COUNTIFS($A:$A,$E$4,$B:$B,">=18:00",$B:$B,"<=05:59")

I've attached the sample Doc for reference,Kindly do a needful.

Thanks!

 

[GraH - Guido]

might be
=COUNTIFS($A:$A,$E5,$B:$B,">=18:00")+COUNTIFS($A:$A,$E5,$B:$B,"<=05:59")

Since there are no dates, there is no logic before/past midnight.

 

[Chihiro]

Conditions in COUNTIFS is considered "AND" condition. Therefore, it must satisfy all criteria. However, time cannot satisfy both being >=18:00 & <=5:59.

To avoid this you must either use addition (OR condition) as Grah - Guido has shown above. Which I'd recommend for readability and performance.

Or use other formula construct. Such as SUM(IF(Conditions,1,0)) array formula or Sumproduct formula.

Ex:
=SUMPRODUCT(($A$4:$A$387=E5)*($B$4:$B$387>=TIME(18,0,0))+($A$4:$A$387=E5)*($B$4:$B$387<TIME(6,0,0)))

 

[vletm]

saravana
... almost same as above
... can time be same time after 18:00 and before 06:00 ... ?

 

[GraH - Guido]

What I meant and awkwardly expressed, so much better explained by Chihiro and @vletm. Thx guys.

 

[saravana]

GraH - Guido - Thank you so much, I got the answer