,Countifs -Multiple conditions

[NoviceKB]

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

[#2]

@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

[#3]

@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

[#4]

@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.