Countifs Function

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!

Accepted answers

anirudh

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

All comments

anirudh

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

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to bring Row data into Columns?
Hi, Can you help me, how can we get row data into columns using Unique ID? Per attached below first snapshot, I want to bring rountingOrder 4, RecipientName, Sent and Completed dates in 1 row as show in sanpshot 2. Do I need to create Hierarchy list and Map?
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…