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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
974
Office Version
  1. 2016
Platform
  1. Windows
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,"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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
 

NoviceKB

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
The formulas worked like magic! Many many thanks for your prompt support Peter. Really grateful! Have a nice weekend!
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,719
Messages
5,524,459
Members
409,582
Latest member
riibuildersinc

This Week's Hot Topics

Top