Countifs - Multiple Conditions (HR Dashboard)

[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

 

[Toadstool]

Hi NoviceKB,

I believe this does what you ask.

NoviceKB.xlsx
	C	D	E	F	G	H	I	J	K	L	M
1	Employee Class		Gender			Contract Start Date	Contract End Date		Male	Female	Total Active
2	Expat - Germany		Male			02-Feb-15	21-Jul-16		5	3	8
3	Expat - Germany		Female			06-Aug-18	17-Jun-19
4	Expat - Germany		Male			11-Jan-19
5	Spain		Male			11-Aug-19
6	Expat - Germany		Male			25-Dec-19	19-Nov-20
7	Expat - Germany		Male			05-Jun-19
8	Expat - Germany		Male			26-Oct-17	12-Jun-18
9	Expat - Germany		Female			28-Oct-19
10	Expat - Germany		Male			07-Jul-20
11	Internship		Female			15-Oct-19
12	Expat - Germany		Male			15-Jan-20
13	Expat - Germany		Female			23-Aug-19
14	Expat - Germany		Male			25-Feb-19	24-Jul-20
15	Expat - Germany		Female			29-Apr-18	04-Jan-19
16	Expat - Germany		Male			26-Feb-18	26-Mar-20
Sheet1

Cell Formulas
Range	Formula
K2:L2	K2	=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)
M2	M2	=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]

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
	C	D	E	F	G	H	I	J	K	L	M
1	Employee Class		Gender			Contract Start Date	Contract End Date		Male	Female	Total Active
2	Expat - Germany		Male			2/02/2015	21/07/2016		5	3	8
3	Expat - Germany		Female			6/08/2018	17/06/2019
4	Expat - Germany		Male			11/01/2019
5	Spain		Male			11/08/2019
6	Expat - Germany		Male			25/12/2019	19/11/2020
7	Expat - Germany		Male			5/06/2019
8	Expat - Germany		Male			26/10/2017	12/06/2018
9	Expat - Germany		Female			28/10/2019
10	Expat - Germany		Male			7/07/2020
11	Internship		Female			15/10/2019
12	Expat - Germany		Male			15/01/2020
13	Expat - Germany		Female			23/08/2019
14	Expat - Germany		Male			25/02/2019	24/07/2020
15	Expat - Germany		Female			29/04/2018	4/01/2019
16	Expat - Germany		Male			26/02/2018	26/03/2020
Sheet1

Cell Formulas
Range	Formula
K2:L2	K2	=IFERROR(ROWS(FILTER($E2:$E16,($E2:$E16=K1)*($H2:$H16<=TODAY())*(($I2:$I16>=TODAY())+($I2:$I16=""))*($C2:$C16<>"Spain"))),0)
M2	M2	=K2+L2

 

[NoviceKB]

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
	C	D	E	F	G	H	I	J	K	L	M
1	Employee Class		Gender			Contract Start Date	Contract End Date		Male	Female	Total Active
2	Expat - Germany		Male			2/02/2015	21/07/2016		5	3	8
3	Expat - Germany		Female			6/08/2018	17/06/2019
4	Expat - Germany		Male			11/01/2019
5	Spain		Male			11/08/2019
6	Expat - Germany		Male			25/12/2019	19/11/2020
7	Expat - Germany		Male			5/06/2019
8	Expat - Germany		Male			26/10/2017	12/06/2018
9	Expat - Germany		Female			28/10/2019
10	Expat - Germany		Male			7/07/2020
11	Internship		Female			15/10/2019
12	Expat - Germany		Male			15/01/2020
13	Expat - Germany		Female			23/08/2019
14	Expat - Germany		Male			25/02/2019	24/07/2020
15	Expat - Germany		Female			29/04/2018	4/01/2019
16	Expat - Germany		Male			26/02/2018	26/03/2020
Sheet1

Cell Formulas
Range	Formula
K2:L2	K2	=IFERROR(ROWS(FILTER($E2:$E16,($E2:$E16=K1)*($H2:$H16<=TODAY())*(($I2:$I16>=TODAY())+($I2:$I16=""))*($C2:$C16<>"Spain"))),0)
M2	M2	=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]

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.