I have done a google search and found a couple articles discussing doing a countIF or count(if. However, I still can't use those concepts to get the numbers I need. I need to know how many new hires we had by week, and how many terms, as well as an overall headcount.
Step one is I have a scorecard that has week beginning in column J.
J3- 4/22/12
J4- 4/29/12
J5- 5/6/12
J6- 5/13/12
J7- 5/20/12
J8- 5/27/12
J9- 6/3/12
J10- 6/10/12
On 5/7 there was a new hire class of 13, and on 5/29/13 we added 27 more employees. There is a roster that has all of the employee's and their names as well as a class date (5/29/12 or 5/7/12 so far).
What I was trying to do is count how many Class Dates are between J3 and J3+7 (0). However when J5 is the week value it should pull in 13. When J8 is used it should be 27, all other weeks should be 0. It's count range of class dates is '[Team Roster.xls]Agents'!$G:$G.
Summary: Need count if class date ('[Team Roster.xls]Agents'!$G:$G) greater than or equal to week start, AND less than or equal to week start + 7 (Week start is column J). Hopefully this made sense, any and all help is greatly appreciated.
My main area of confusion is I can put a count(if together for the criteria I am testing, but if both are true, what do I put for the true formula? When I put ('[Team Roster.xls]Agents'!$G:$G) it returns 40 on 5/27/12 and 1 on all other weeks.
EDIT: This is my current formula:
=COUNT(IF(AND('[Team Roster.xls]Agents'!$G$1:$G$3000>=J8,('[Team Roster.xls]Agents'!$G$1:$G$3000<=J8+7)),'[Team Roster.xls]Agents'!$G$1:$G$3000))
Bookmarks