Counting Headcounts of Male Fulltime Employees Using IFTHEN Formula

agk729 · 2025-02-26T09:03:51+00:00

There was an error rendering this rich post.

Hello,

I am having trouble counting the number of headcounts using the IFTHEN formula in Anaplan.

My formula:

IF '01_PSFT・Excel data'.Contract = "Fulltime" AND '01_PSFT・Excel data'.Gender = "Male" THEN 1 ELSE 0

What I want to count: people to be categorized as both
・Contract column:Fulltime
・Gender column:Male

I created the list "06_Names" that has parents for each name, but I received the following message:

Invalid formula for '01_KPI Reporting'.'Parament Headcount-Male': Automatic sum of '01_PSFT・Excel data'.Contract over 06_Names hierarchy is not possible as 06_Names does not have a built-in top level

Please kindly let me know the way to fix formulas or the lists etc.

①Modules that I want to insert formula

②Modules as a data source of ①module

③The grid view of 06_Names list

Accepted answers

All comments

devrathahuja

Hi @agk729 ,
Calculate the count in module 2 with the formula you have & then pull that line item into module 1. It'll auto sum it & show you the total value in module 2.

agk729

@devrathahuja
Thank you for your reply.

(Your reply) Calculate the count in module 2 with the formula you have & then pull that line item into module 1. It'll auto sum it & show you the total value in module 2.

>Do you mean to put the formula below into the highlighted ② module?
formula:

IF '01_PSFT・Excel data'.Contract = "Fulltime" AND '01_PSFT・Excel data'.Gender = "Male"THEN 1ELSE 0

Since this module is input data for the 01_KPI Reporting module, how can I count with the formula above?

It seems Anaplan doesn't have a count formula like Excel. How can I count the items in Anaplan?

Note: I merged two CSV files into one module.

I am deeply appreciate your support.

devrathahuja

Hello @agk729 ,
Yes exactly, create a new line item & put the formula in this one. The 1 in your formula will act as the count.

SriNitya

seymatas1

Hi @agk729,

In addition to suggestions of @SriNitya and @devrathahuja, I recommend this approach for scalability, performance, and best practices.

Create two lists:

List 1: Gender

List items: Male, Female

List 2: Employment Type

List items: Contract, Part-time, Full-time

2. Create a LOOKUP module:

Line Items:

Male, format: Gender list, Male is selected from the dropdown
Full-time, format: Employment Type list, Full-time is selected from the dropdown

3. Create a filter module or two Boolean line items in the existing module: Male? and Full-time?.

Male?

Formula:
Gender = LOOKUP Module. Gender

Full-time?

Formula:

Employment Type = LOOKUP Module. Employment Type

Count

Formula:

If Male? AND Full-time? THEN 1 ELSE 0

Avoid hardcoding, and consider scalability and performance:

Text is not a memory-friendly data type; minimize the use of it as much as possible. If statements with hard-coded text will slow down your model.

Hard coding is always a pain when it comes to maintenance. A typo can break down your formula.

At some point, there will be a requirement to count, female - full-time, male-contract, etc. Your Employment Type list will also grow: Hourly, Intern, etc. This solution is more scalable. It is just a matter of adding another list item, Lookup or Boolean.

I hope this helps.
Seyma 🌷🙂

agk729

Thank you for your continued support.

I have tried all the suggested approaches, but I have not succeeded yet.

Approach 1 & 2: @devrathahuja @SriNitya
When I entered the formula:

IF Gender = "Male" AND Contract = "Fulltime"THEN 1 ELSE 0

The following error message appears:

The formula for '01_PSFT・Excel data'.count is invalid:Datatypes do not match for '=' function: LIST:02_Gender, TEXT

Approach①②

Approach 3: @seymatas1
I created two lists:

Gender
Employment Type

I created both lists and made line items as shown in the screenshot below.

When I tried to create two Boolean line items in the existing module, the formulas were not authorized. The formulas were:

"Male?": Gender = LOOKUP Module.Gender"Fulltime?": Employment Type = LOOKUP Module.Employment Type

The following error message appears:

The formula for '01_PSFT・Excel data'.Fulltime? is invalid:Fulltime? = Employment Type = LOOKUP Module.Employment TypeEmployment Type is not a recognized line item or list member

Approach ③

FYI:
List 1 Gender

List 2

Although this thread takes a bit time, I deeply appreciate your support!

devrathahuja

Hi @agk729 ,
If you're going for approach 1/2 use name(gender)="Male" and same for the other line item as we need to convert the list format to a text format first.

I also suggest using a more dynamic approach (approach 3) as suggested by @SeymaTas, can you also share the lookup module? The lookup module should not have any dimension & the line item format should be the same list - Gender & Employment type.

It'll then match both the list members & give you a true for that boolean. Here you can directly refer to the line item instead of using name(gender), so the formula will be gender line item = male line item in lookup module.

Hope this helps! :)

agk729

Thank you for your reply.

I attempted to use the LOOKUP module for the "Male?" line item with the following formula:

IF LOOKUP(Gender) = "Male" THEN 1 ELSE 0

However, I encountered an error. The formula for '01_PSFT・Excel data'.Male? is invalid:

Male? = IF    LOOKUP(Gender) = "Male"THEN    1ELSE    0

The error message states that 'LOOKUP' is not a recognized function name.

	The formula for '01_PSFT・Excel data'.Male? is invalid: Male? = IF LOOKUP(Gender) = "Male" THEN 1 ELSE 0 'LOOKUP' is not a recognized function name

Screenshot of the module which I want to insert the formula:

devrathahuja

Hi @agk729 ,
You don't need to use lookup in the formula. You need a SYS Lookup module with line items for Gender & Contract Type.

The formula for the Male Boolean line item then needs to be Gender (in the module 1 - 01_PSFT…) = Gender (In the SYS Lookup Module)

Same for the contract type.

agk729

@devrathahuja
Thank you for very much for the instruction.

I have created the LOOKUP module as shown in the screenshot below and successfully entered the formula.

However, even though the formulas were installed, the booleans do not appear in the "Male?" and "Female?" lines. I am unable to understand why the "Fulltime?" line correctly shows the result.

In this case, how should I change the formulas ? Or are the lists and modules have problems ?

①Grid view

②Lookup module

③1_PSFT・Excel data Module

devrathahuja

Hi @agk729 ,
Have you selected "Male" in the lookup module line item? It'll work exactly in the same manner as the Part time vs full time employee boolean :)

agk729

@devrathahuja @seymatas1
Thank you for your assistance !

When I added each items in the SYS LOOKUP Module as you mentioned, the formula correctly worked on 01_PSFT・Excel data module.

It seems I have to crete lines for each item on the Lookup module in Anaplan...

devrathahuja

@agk729 - Yes, that is correct, 1 for each item you want to flag.

Alternatively you can store the Male/Female & Parttime/Fulltime boolean flags in their SYS modules too so you can in future refer whereever requried.

Quick Links

Can you help?

Unanswered questions

Not Able to Join the Level 1 Model Building Course
Hey Everyone, I was trying to learn about the Level 1 Model Building Course, when i log in to my system and click on "Start the course" option its showing training unavailable, Can anyone please help me with this @Dikshant and all?
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…