Aug 11 2021 04:56 AM
I've managed to get most of my countifs formulas to work except 1.
Use Case:
I have employee records going back 10 years, in this records in
Gender, Start Date, End Date (null if still current), Age
I have another table with each year broken down to months
2020 Jan - Dec
2019 Jan - Dec
2018 Jan - Dec etc
I want to be able to work out what the female percentage was month by month going back the 10 years.
I've been able to work out new starters, terminations etc based on a month as they're comparing dates in countifs and edate function but I can't get it to count the amount of Males and Females in a month base of if they were current at the time. I want to also work out average age for that month of my employees but I suspect that should be easy with the correct structure.
Can anyone help me please!
Aug 11 2021 06:51 AM
For consistency, change A11, A15 and A19 to Female.
In B10 as an array formula confirmed with Ctrl+Shift+Enter:
=SUMPRODUCT((Table13[Column1]=$A10)*(Table13[Start Date]<=EOMONTH(B$9,0))*IF(Table13[End Date]="",TRUE,Table13[End Date]>=B$9))
Fill down to B11, then to the right to column M.
Similarly in B14:
=SUMPRODUCT((Table13[Column1]=$A14)*(Table13[Start Date]<=EOMONTH(B$13,0))*IF(Table13[End Date]="",TRUE,Table13[End Date]>=B$13))
and in B18:
=SUMPRODUCT((Table13[Column1]=$A18)*(Table13[Start Date]<=EOMONTH(B$17,0))*IF(Table13[End Date]="",TRUE,Table13[End Date]>=B$17))
Your sample workbook contains gender in the age column, but apart from that: do you want to use the current age, or the age during the months? You'd need the date of birth for the latter...
Aug 11 2021 06:59 AM
@gillsy I think I see you problem. In order to calculate it you have to have an OR condition to take care of the blank end date so you could create a helper column being EndDate-Today and that would be something like: =IF(TABLE13[End Date],TABLE13[End Date],TODAY()) and then you could use the COUNTIFS and AVERAGEIFS function. That would make it a bit cleaner and easy to follow and instead of TODAY() you could enter a future date to assume they will still be with you through the end of the year or next, ... (that was a question I had about your dates in the future if present employees should be assumed to stay)
In the attached I used different formulas instead to give you options (again the above is more straight forward).
=SUMPRODUCT((Table13[Column1]=$A10)*(Table13[Start Date]<B$9)*IF(Table13[End Date],Table13[End Date]>B$9,1))
and for the Average Age (I assume the average # days the current employees have been employed)
=AVERAGE(FILTER(B$9-Table13[Start Date],(Table13[Start Date]<B$9)*IF(Table13[End Date],Table13[End Date]>B$9,1),0))
So other considerations will be how to handle employees that started or ended in that particular month, but I'll leave that up to you.
good luck.
Aug 11 2021 07:05 AM
Aug 11 2021 07:20 AM
I didn't even notice the two Novembers... :)
Aug 11 2021 03:24 PM - edited Aug 11 2021 03:26 PM
Thanks to both of you, @mtarler I couldn't get yours to work but I understand the concept,
@Hans Vogelaar yours initially wasn't quite right as it was adding the employee the month after they started so I modified it slightly to fit it. I had a feeling SUMPRODUCT was going to be in the formula somewhere.
The final formula being =IF(SUMPRODUCT((Table13[[Gender]:[Gender]]=$A10)*(Table13[[Start Date]:[Start Date]]<(EDATE(B$9,1))*IF(Table13[[End Date]:[End Date]]="",TRUE,(IF(Table13[[End Date]:[End Date]]="",TRUE,Table13[[End Date]:[End Date]]>=B$9)))))=0,"",SUMPRODUCT((Table13[[Gender]:[Gender]]=$A10)*(Table13[[Start Date]:[Start Date]]<(EDATE(B$9,1))*IF(Table13[[End Date]:[End Date]]="",TRUE,(IF(Table13[[End Date]:[End Date]]="",TRUE,Table13[[End Date]:[End Date]]>=B$9))))))
Cheers for both your help, I was stuck on that for almost 8 hours. It's a new formula to add to my knowledge.
Aug 11 2021 11:11 PM
@Hans Vogelaar I'm stuck on averaging the age now.
I want an average age based on the month in question.
Aug 12 2021 02:52 AM
In B24 as an array formula confirmed with Ctrl+Shift+Enter:
=AVERAGE(IF((Table134[DOB]<>"")*(Table134[Start Date]<=EOMONTH(B23,0))*IF(Table134[End Date]="",TRUE,Table134[End Date]>=B23),(B23-Table134[DOB])/365.25,""))
Fill or copy to the other cells.