Jan 06 2022 04:57 AM
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.
Jan 06 2022 05:32 AM
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.
Jan 06 2022 05:33 AM
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)