Countifs Function

chseverson
chseverson
in Modeling

Hello, 

 

I've researched this topic but haven't been able to find a clear answer. I'm looking to create a headcount dashboard that tracks historical headcount by function over time (by month). It looks as though I'll need to combine an "IF THEN" and a "SUM" function. I am referencing a module that has:

 

Employee Department

Start Date

End Date

Transfer In/Out Dates

 

I am seeking to simply create a grid with Departments as Rows and months as Columns with the number of employees active as of the end of the month in each department. This would be a straightforward Countifs within Excel, but I am struggling to create this logic within Anaplan. Does anyone have any advice or similar experiences? Thanks so much!

Tagged:

Best Answer

  • anirudh
    anirudh
    Answer ✓
    Hi ch,

    You do not need to create another module, simply create a new line item called Headcount in the model with dept and employees as dimensions. However, add the dimension of month to this line item in the time scale option.

    The following formula should work:

    IF PERIOD(Join Date) <= ITEM(Time) AND PERIOD(Leave Date) >= ITEM(Time) THEN 1 ELSE 0

    If you need to display this in a dashboard, simply hide the employees dimension and lock it to all employees

    Regards,
    Anirudh

Answers

  • PaulRitner
    PaulRitner

    Hi, 

     

    Just to piggyback on Anirudh:  I'll usually label the module containing the Start/End dates as Employee Setup or Staffing Management, and then have a companion module (with months) where, based on the Employee Setup information, we calculate our monthly Headcount & Compensation amounts.  

     

    Anirudh's formula is a good introductary example for calculating month end headcount, however, Start & End Dates may be blank, so the formula probably needs to look at that.

     

    I usually do something like: 

    If isblank(Join Date) then 0 Else if PERIOD(Join Date) <= ITEM(Time) AND (PERIOD(Leave Date) >= ITEM(Time) or Isblank(Leave Date)) THEN 1 ELSE 0

     

    Note: 

    • If Join Date is missing, then I don't make any assumptions about whether the individual is active or not... in this sense, a Join Date is required in order to calculate any headcount.
    • If Leave Date is missing, then I assume the individual is active... therefore, a Leave Date is not required for people who have joined, but haven't left yet.

     

    Cheers!

     

    Paul

Categories