Jul 15 2020 06:41 AM - edited Jul 15 2020 01:55 PM
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 NOT "Adecco" nor Blank.
2. The Contract Startdate is less than TODAY(todays date) and not blank.
3. Probation status is NOT "Terminated" (If status is Terminated but the contract enddate is later than todays date or BLANK, this should still count as 1 Headcount)
Currently this is my formula:
=COUNTIFS(B:B,"<>Adecco",B:B,"<>",C:C,"<=TODAY()",E:E,">=TODAY()",F:F,"<>Terminated")
But it returns zero... :( :(...Any ideas will be very welcome.
Thanks.
Jul 15 2020 11:39 AM
Could I offer a suggestion? Let's make it simpler.
Your set of conditions is complex enough that you might be better suited to the use of "helper columns" to test one or at most two conditions and yield a "Y" or some other value, that could then be used as the basis for counting.
See the attached. I didn't try to verify the number...will leave that to you.
Jul 15 2020 12:16 PM - edited Jul 15 2020 12:32 PM
These two statements appear to cancel each other out:
You seem to be saying that regardless of the probation status, if the probation end date is greater than today or blank, then it should count as a headcount.
If you mean to say that the probation end date must not be before today (i.e. it can be today, after today, or empty), then use this:
=COUNTIFS(Table1[Employee Class],"<>Adecco",Table1[Employee Class],"<>",Table1[Contract Start Date],"<="&TODAY(),Table1[Probation Enddate],">="&TODAY(),Table1[Probation Status],"<>Terminated")+COUNTIFS(Table1[Employee Class],"<>Adecco",Table1[Employee Class],"<>",Table1[Contract Start Date],"<="&TODAY(),Table1[Probation Enddate],"",Table1[Probation Status],"<>Terminated")
This is actually summing these two counts:
1. Non-Adecco employees whose class is known and whose contract start date is before or on today with a probation date on or after today who have not been terminated, AND
2. Non-Adecco employees whose class is known and whose contract start date is before or on today with no probation date who have not been terminated
Jul 15 2020 01:56 PM
@OwenPrice Many thanks for your observation and you are absolutely right!
I have modified my initial submission. Can you take a look and let me know if this makes sense to you? It does to me now.
If yes, how can I capture this with a formula?
Thanks.
Jul 15 2020 03:12 PM
Can you confirm this is what you mean? If we break each condition down into bullets of their own:
Regarding the bold items - should those actually be probation end date? I wonder whether the actual situation you're trying to capture here are people who have been terminated but they have not finished their probation yet, so they are still active headcount.
Jul 15 2020 04:42 PM
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!
Jul 15 2020 07:55 PM
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.