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).
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 11, 2021

      mtarler 

      I didn't even notice the two Novembers... 🙂

      • gillsy's avatar
        gillsy
        Copper Contributor
        Aug 11, 2021

        HansVogelaar  mtarler 

         

        Thanks to both of you, mtarler I couldn't get yours to work but I understand the concept, 

         

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

Resources