Stuck on a countifs formula

[gillsy]

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!

[#2]

@gillsy 

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...

[#3]

@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.

[#4]

I see Hans submitted while I was working on it and typing the response. You'll notice his formula is very similar to mine but he use EOMONTH() which again is that issue/question about including or excluding employees that start/end that month.
Also, I forgot to also mention the typo of "Females" instead of "Female" (as Hans noted) but also each year needed a December (month 12) instead of 2 Novembers (month 11).

[#5]

@mtarler 

I didn't even notice the two Novembers... :)

[#6]

@Hans Vogelaar  @mtarler 

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.

[#7]

@Hans Vogelaar  I'm stuck on averaging the age now. 

I want an average age based on the month in question.

[#8]

@gillsy 

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.