Forum Discussion
Using Countifs to Calculate Headcounts
Hi Team, In my attached screenshot I am wondering how I can use COUNTIFS to calculate the current Headcount of employees based on the following conditions. Countifs; 1. The employee class is N...
Hi OwenPrice !
Yes to points 1 through 4.
Regarding your point 5, as you see from my test sheet, the probation column will have those who have completed probation or have their probation ongoing. These 2 categories of staff should of course be counted as part of the current HC. What I wanted to capture in addition to the above two categories is exactly what you mentioned in your point 5.2.1. Normally once we terminate someones probation we align the contract enddate to the probation enddate but for the sake of the formula please use only PROBATION ENDDATE though.
Thanks and have a goodnight!
Your support is much much appreciated!
I think this formula will do what you need. It returns 24 rows from your sample data:
=COUNTIFS(Table1[Employee Class],"<>Adecco",Table1[Employee Class],"<>",Table1[Contract Start Date],"<="&TODAY(),Table1[Contract Start Date],"<>",Table1[Probation Status],"<>Terminated")+COUNTIFS(Table1[Employee Class],"<>Adecco",Table1[Employee Class],"<>",Table1[Contract Start Date],"<="&TODAY(),Table1[Contract Start Date],"<>",Table1[Probation Status],"Terminated",Table1[Probation Enddate],">="&TODAY())
Please see attached.