Trying to count employees based on certain criteria

**JasonRobbins** · 07-08-2012, 03:50 PM

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...

**zbor** · 07-08-2012, 03:53 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.