Countifs - Multiple Conditions (HR Dashboard)

NoviceKB

New Member
Joined
May 22, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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


Sample data.PNG
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi NoviceKB,

I believe this does what you ask.

NoviceKB.xlsx
CDEFGHIJKLM
1Employee ClassGenderContract Start DateContract End DateMaleFemaleTotal Active
2Expat - GermanyMale02-Feb-1521-Jul-16538
3Expat - GermanyFemale06-Aug-1817-Jun-19
4Expat - GermanyMale11-Jan-19
5SpainMale11-Aug-19
6Expat - GermanyMale25-Dec-1919-Nov-20
7Expat - GermanyMale05-Jun-19
8Expat - GermanyMale26-Oct-1712-Jun-18
9Expat - GermanyFemale28-Oct-19
10Expat - GermanyMale07-Jul-20
11InternshipFemale15-Oct-19
12Expat - GermanyMale15-Jan-20
13Expat - GermanyFemale23-Aug-19
14Expat - GermanyMale25-Feb-1924-Jul-20
15Expat - GermanyFemale29-Apr-1804-Jan-19
16Expat - GermanyMale26-Feb-1826-Mar-20
Sheet1
Cell Formulas
RangeFormula
K2:L2K2=COUNTIFS($H$2:$H$16,"<="&TODAY(),$C$2:$C$16,"<>"&"Spain",$I$2:$I$16,">="&TODAY(),$E$2:$E$16,K$1)+COUNTIFS($H$2:$H$16,"<="&TODAY(),$C$2:$C$16,"<>"&"Spain",$I$2:$I$16,"",$E$2:$E$16,K$1)
M2M2=COUNTIFS($H$2:$H$16,"<="&TODAY(),$C$2:$C$16,"<>"&"Spain",$I$2:$I$16,">="&TODAY())+COUNTIFS($H$2:$H$16,"<="&TODAY(),$C$2:$C$16,"<>"&"Spain",$I$2:$I$16,"")
 
Upvote 0
If your Excel 365 has the FILTER function ...

(BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.)

NoviceKB 2020-06-18 1.xlsm
CDEFGHIJKLM
1Employee ClassGenderContract Start DateContract End DateMaleFemaleTotal Active
2Expat - GermanyMale2/02/201521/07/2016538
3Expat - GermanyFemale6/08/201817/06/2019
4Expat - GermanyMale11/01/2019
5SpainMale11/08/2019
6Expat - GermanyMale25/12/201919/11/2020
7Expat - GermanyMale5/06/2019
8Expat - GermanyMale26/10/201712/06/2018
9Expat - GermanyFemale28/10/2019
10Expat - GermanyMale7/07/2020
11InternshipFemale15/10/2019
12Expat - GermanyMale15/01/2020
13Expat - GermanyFemale23/08/2019
14Expat - GermanyMale25/02/201924/07/2020
15Expat - GermanyFemale29/04/20184/01/2019
16Expat - GermanyMale26/02/201826/03/2020
Sheet1
Cell Formulas
RangeFormula
K2:L2K2=IFERROR(ROWS(FILTER($E2:$E16,($E2:$E16=K1)*($H2:$H16<=TODAY())*(($I2:$I16>=TODAY())+($I2:$I16=""))*($C2:$C16<>"Spain"))),0)
M2M2=K2+L2
 
Upvote 0
If your Excel 365 has the FILTER function ...

(BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.)

NoviceKB 2020-06-18 1.xlsm
CDEFGHIJKLM
1Employee ClassGenderContract Start DateContract End DateMaleFemaleTotal Active
2Expat - GermanyMale2/02/201521/07/2016538
3Expat - GermanyFemale6/08/201817/06/2019
4Expat - GermanyMale11/01/2019
5SpainMale11/08/2019
6Expat - GermanyMale25/12/201919/11/2020
7Expat - GermanyMale5/06/2019
8Expat - GermanyMale26/10/201712/06/2018
9Expat - GermanyFemale28/10/2019
10Expat - GermanyMale7/07/2020
11InternshipFemale15/10/2019
12Expat - GermanyMale15/01/2020
13Expat - GermanyFemale23/08/2019
14Expat - GermanyMale25/02/201924/07/2020
15Expat - GermanyFemale29/04/20184/01/2019
16Expat - GermanyMale26/02/201826/03/2020
Sheet1
Cell Formulas
RangeFormula
K2:L2K2=IFERROR(ROWS(FILTER($E2:$E16,($E2:$E16=K1)*($H2:$H16<=TODAY())*(($I2:$I16>=TODAY())+($I2:$I16=""))*($C2:$C16<>"Spain"))),0)
M2M2=K2+L2
Oh wow. Wasnt aware of that. Will checkout XL2BB.

The formulas worked like magic! Many many thanks for your prompt support Peter. Really grateful! Have a nice weekend!
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top