Counting Cells dependant on data

  • Thread starter Thread starter skyshadow
  • Start date Start date
S

skyshadow

I have set up a scheduling worksheet and i want the worksheet to tell me
how many people i have during certian time periods. The information is
set up like so

a1=time in
b1=blank
c1=time out

Cell formating is custom (hA/P) So it displays as 6a or 5p

I want another cell to check how many people are scheduled during a
certian shift say 6am to 11am and return a count of those people. Is
this possible? If so please help and thanks
 
Hi
you can use the following formula:
=SUMPRODUCT((A1:A99<=TIME(6,0,0))*(C1:C99>=TIME(11,0,0)))
format the target cell for this formula as general or number

HTH
Frank
 
ok...that almost worked. It doesn't seem to count certian time periods.
Example i have a time period set for 20:30-22:00

=SUMPRODUCT((T4:T38<=TIME(20,30,0))*(V4:V38>=TIME(23,0,0)))

and i have 4 employees scheduled 3 from 5pm to 10pm and one from 5pm to
9pm and with that formula it returns 3 instead of 4. All the formating
is the same for each of the time based cells and the cell with the
above formula is formatted general. Is there something i missed?
 
Hi

first I think (accroding to your example) you entered the following
formula:
=SUMPRODUCT((T4:T38<=TIME(20,30,0))*(V4:V38>=TIME(22,0,0)))
so the shigt ends at 10pm (and not at 11 pm)

The result Excel gave you is correct (in respect to your example data).
The employee who works from 5pm to 9pm does not fulfill the seconde
condition (>=10pm). So he is not counted as he does not work the full
shift from 5pm to 10pm

If you'll try to count all employees who work at least part-time during
your shift you have to use the following formula:
=SUMPRODUCT((T4:T38<=TIME(20,30,0))*(V4:V38>TIME(20,30,0))) +
SUMPRODUCT((T4:T38>TIME(20,30,0))*(T4:T38<TIME(22,0,0)))

HTH
Frank
 
Back
Top