Forum Discussion

NoviceKB's avatar
NoviceKB
Brass Contributor
Jun 18, 2020

,Countifs -Multiple conditions

Dear Team,

 

I am currently working on an HR Dashboard and I want to be able to use a formula to do 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?

 

Thanks in advance for the usual support.

 

Regards,

KB

excel
Formulas and Functions
office 365

3 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor
    Jun 18, 2020

    NoviceKB 

     

    Here's a start, but I want to check before going further to see if you have a current enough version of Excel for you to be able to take advantage of the newer Dynamic Array functions. In this case, I use the FILTER function to sift through the employee list and see which ones meet your criteria.

     

    Here's the formula:

    =COUNTA(FILTER(G4:G20,H4:H20<=TODAY()*(F4:F20<>"Spain")))

     

    This formula can have additional criteria added if needed. So, for example, if you wanted to count Males or Females distinctively, you could add that criterion as follows

    =COUNTA(FILTER(G4:G20,H4:H20<=TODAY()*(F4:F20<>"Spain")*(E4:E20="Male")))

    note, the * sign between the sets of criteria serves as an AND. If you wanted the equivalent of an OR you use the "+"

     

     

    I'm

    • NoviceKB's avatar
      NoviceKB
      Brass Contributor
      Jun 18, 2020

      mathetes 

       

      Yes I have the newest Excel 365 with FILTER function. First time I actually heard of it. WIll read more on it! The formula worked like a charm!

       

      Thanks!

       

      NoviceKB

      • mathetes's avatar
        mathetes
        Gold Contributor
        Jun 18, 2020

        NoviceKB 

         

        Here's a link to a YouTube video that was my own introduction not all that long ago. These are very powerful and useful functions... enjoy!

         

        https://www.youtube.com/watch?v=9I9DtFOVPIg

         

        As a postscript, I realize that I'd not made the formula smart enough to eliminate those whose contracts had ended, but for now I'll assume that what I did give you enables you to go ahead and fine tune. Come back with questions if you still need help.

Resources