Tracking Open Recruitment Roles Monthly

Occasional Visitor

I have created a KPI sheet for my organization, tracking Active Employees by month, hires, exits, turnover, etc. I have the spreadsheet build, and I have collums E-P with the Month on it, and I have been using the EOMONTH to ensure I get the numbers based on the end of that month. 


I have Collums R where I list all the jobs, Collums S has the date the role opened, and collums T has the closed date.


What i'm looking for is a formula where the total number of open jobs (jobs without a close date) will count each month, but once there is a close date, it will stop counting. This way I would have a month over month view of how many jobs were active over the year. I can't seem to figure out how to do this.

2 Replies




Hope I was able to help you with this info.



I know I don't know anything (Socrates)


* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.


Assuming in E2:P2 you have dates (end of month) formatted as months, and you have no empty rows in the range. Formula could be like

=COUNTIFS($S$3:$S$15, "<=" & E$2, $T$3:$T$15, ">=" & E$2)