Forum Discussion
Solved
Using MS Excel to Calculate Rolling Headcount and Turnover
I have an Excel spreadsheet with columns including: personnel names date started date left (blank cell if still current) duration calculation (=IF( [Date Left] >0, [Date Left] - [Date Started] ...
1) In H2, enter the following array formula confirmed with Ctrl+Shift+Enter:
=SUMPRODUCT(($B$2:$B$11<=G2)*(IF($C$2:$C$11="",G2,$C$2:$C$11)>=G2))
Fill down.
In O2:
=COUNTIFS($B$2:$B$11,">="&M2,$B$2:$B$11,"<"&EDATE(M2,1))
In P2:
=COUNTIFS($C$2:$C$11,">="&M2,$C$2:$C$11,"<"&EDATE(M2,1))
Fill down.
Thank you so much, I really appreciate your help!