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.
--
Amin

=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" wrote:

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.
--
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?
--
Amin


"Sean Timmons" wrote:

=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" wrote:

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.
--
Amin

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