Trying to count employees based on certain criteria

Holy god, I've been trying to work on this formula for an entire DAY and it's driving me insane, literally.

I've attached what I've been working on. Essentially, I want to count employees based on their status full time employee (FTE) or Contractor. However, I only want to count those people IF they are working within certain dates. For instance, I want to count FTEs that started before the dates in row 6 but will not be counted if the projected end date in column K is less than or equal to the dates in row 6 starting in column U. OR I want to count contractors the same way in row 20 based on the same date criteria.

Example, say we hired a FTE on December 20,2011. And this employee worked till January 17. I want the spreadsheet to show that (since I have the dates grouped by week) there will be 1 FTE on January 7 (U19) and January 14 (V19) but 0 employees on January 21 (W19). But I only want that specific employee counted in the total row IF in coloumn E that employee has a corresponding FTE identifier. If the employee is labeled as a contractor, i dont want it counted in row 19, obviously. Same for contractors in row 20, only want "contractors" between certain dates counted.

Also, we don't know when some employees will leave, so I left the projected end date blank. If there is no end date, I don't want that to matter, however if there is an end date, I want the formula to recognize it and apply the condition. So if I had an employee who was hired and we don't know when he will leave, I want the formula to just project out forever that we will have that employee.


It's just a simple head counter with criteria but I've tried countif, countifs, sumproduct, sum(if)....EVERYTHING...

By helping me you are seriously saving a life...I'm about to go crazy...