counting how many employees are scheduled to work by hour

  • Thread starter Thread starter amin
  • Start date Start date
A

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.
 
=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)))
 
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?
 
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
 
Back
Top