+ Reply to Thread
Results 1 to 2 of 2

HR Dashboard Question_COuntifs

  1. #1
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    HR Dashboard Question_COuntifs

    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
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    Re: HR Dashboard Question_COuntifs

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dashboard help! Data from columns showing in the correct boxes on dashboard
    By MattExcelLearner in forum Excel Formulas & Functions
    Replies: 35
    Last Post: 06-27-2019, 11:10 AM
  2. Dashboard
    By sharugan in forum Excel General
    Replies: 1
    Last Post: 09-12-2010, 12:31 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1