HR Dashboard Question_COuntifs

[Kay Bee]

Dear Team,

I am currently working on an HR Dashboard and struggling with Countifs.

I want to be able to report 2 things:

A. report the overall current number of employees who are still onboard. This will mean as long as I keep updating the datasheet this number should automatically be updating.

Conditions:
1. Their contract should still be active

2. Their startdate cannot be in the future. Must be today or earlier.

3. In column "I" of the attached sample sheet all the blank cells means those staff are permanent employees so they should also count into the current number.

4. Exclude staff with employee class "Spain"


B. The datasheet for the dashboard will always have data of employees whose contract have ended and employees who are still here. Which formula is best to record in a more dynamic way the number of ACTIVE females and males?


How can I go about solving the 2 points above?

Thanks in advance for the usual support.

Regards,


KB

[ORoos]

Hi Kay Bee,

I saw the reference to Male/Female. Count the current females (1), then current males (13), total = F+M (14)
Number of Females:
=COUNTIFS(H4:H20,"<="&TODAY(),I4:I20,">="&TODAY(),E4:E20,"Female")+COUNTIFS(H4:H20,"<="&TODAY(),I4:I20,"",E4:E20,"Female")

Number of Males:
=COUNTIFS(H4:H20,"<="&TODAY(),I4:I20,">="&TODAY(),E4:E20,"Male")+COUNTIFS(H4:H20,"<="&TODAY(),I4:I20,"",E4:E20,"Male")

In regards to exclude 'Spain', did you mean if Country (column B) is Spain ? There is no one with employee class "Spain".

EDIT: Note the formulas should be in one line.