counting how many employees are scheduled to work by hour

[amin]

Hi,

I am using the below formula to count how many employees are scheduled to
work by hour but it is not counting if the shift starts in the PM and ends in
the AM.

=SUMPRODUCT(($B$3:$B$24<=$A36)*($C$3:$C$24>=$A37))

Thank you for your help in advance.

 

[Sean Timmons]

=IF($A36>$A37,SUMPRODUCT(($B$3:$B$24<=$A36)*($C$3:$C$24>=$A37)),SUMPRODUCT(($B$3:$B$24>=$A36)+($C$3:$C$24<=$A37)))

 

[amin]

Thanks Sean for the response.

It is not giving me the desired results. I only have 2 members schedled on
Saturday. One from 10 AM to 9 PM and the other from 10 PM to 8 AM.

A36 is 12:00 AM so the result should be 1.

The formula you provided is giving me 42.

Any ideas?

 

[barry houdini]

Try like this

=SUMPRODUCT((($B$3:$B$24<=$A36)+($C$3:$C$24>=$A37)+($B$3:$B$24>$C$3:$C
$24)=2)+0)

regards, daddy