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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 12, 2021

        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.

Resources