Forum Discussion
Solved
How to Count No. of Staff per hour
please help me! I just want to count the total number of staff per hour per day given the raw data below. please see the desired output below. RAW DATA date date name tin1 tin2 tin3...
I'm not sure I understood your logic correctly since results are bit different, formulas are
IN =SUMPRODUCT(($E$2:$G$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2)) OUT =SUMPRODUCT(($H$2:$J$106>$Q2)*($H$2:$J$106<($Q2+1/24))*($A$2:$A$106=$P2)) # OF STAFF =SUMPRODUCT(($H$2:$J$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))
I'm not sure I understood your logic correctly since results are bit different, formulas are
IN
=SUMPRODUCT(($E$2:$G$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))
OUT
=SUMPRODUCT(($H$2:$J$106>$Q2)*($H$2:$J$106<($Q2+1/24))*($A$2:$A$106=$P2))
# OF STAFF
=SUMPRODUCT(($H$2:$J$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))