Forum Discussion

gillsy's avatar
gillsy
Copper Contributor
Aug 11, 2021

Stuck on a countifs formula

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)...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Aug 11, 2021

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

mtarler's avatar
mtarler
Silver Contributor
Aug 11, 2021
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).

Resources