COUNT How many people are scheduled to take calls

[javijuan]

I am working on a form that calculates how many people are scheduled to take phone calls at any given time.

I have a cell with NOW()-INT(NOW()) to give me the current time...

I also have a list in another sheet (secret) with the roster (Name, Line of Business, dates (M,T,W,J,F...), start time, stop time and where they seat)



I use this formula:

=SUM(COUNTIFS(secret!D:D,"<="&$U$18,secret!E:E,">="&$U$18,secret!F:F,"*M*",secret!C:C,"LOB",secret!G:G,"PB"))



The formula compares the start time with the current time (to see if the start time is less tan the current time) and then compares the stop time with current time to see if they should still be here. Then it checks if today is M (Monday), what Line of business and the quad where they sit.



If everything checks out on a single row it should count it.



The issue is that when I run it it always gives me a value.. but is always less than the actual headcount, which should be the other way around since this is not taking in consideration absent people.

I also run some more generic numbers.. like how many people are scheduled to be here at this time... no matter where they sit or their line of business, which gives me a higher number but I still dont know if they are accurate.



Any help will be greatly appreciated.