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))SergeiBaklan really great help. thank you very much for this.
ChristianMercado , you are welcome
hi SergeiBaklan,
I need your help.
I want to compute for the total hours worked per employee. I used basic formula Time out - Time in but I get negative results when there is a blank cell.
Thank you in advance.
