Countifs formula produces zero value with multiple criteria

[ls3009]

Just like it says on the tin! I am trying to produce a table (below) that shows the number of employees in each department depending their start date and leaving date (leaving date is blank if they are still an employee)

This is my formula: =COUNTIFS(Summary!$F:$F,KPIs!$B33,Summary!$G:$G,"<="&D29,Summary!$H:$H,"")+COUNTIFS(Summary!$F:$F,KPIs!$B33,Summary!$G:$G,"<="&D29,Summary!$H:$H,">="&D30)

This is my source data:

When I try each individual criteria it works fine, but when I combine them it comes out with zero - can anyone help?

Thank you!!

 

[Joe4]

Welcome to the Board!

I have not analyzed your formula and data in great detail, but one thing stuck out to me like a sort thumb.

If you are copying this formula down rows, you will want to lock the row references for D29 and D30, i.e.
you should use D$29 and D$30 instead of D29 and D30 in your formula.

See if that fixes this issue. If not, we will need to do a deeper dive, in which case it would be helpful to actually having sample data we can copy and test on our side, instead of images.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ls3009]

Hello, thank you for the response - I've added in a sample of the data which will hopefully help. I have now locked my date references as suggested but that hasn't worked unfortunately!

Mr Excel - Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1													Employee No.	No.	Forenames	Surname	Job Type	Department	Start Date	Termination Date
2				12/31/22	1/31/23	2/28/23	3/31/23	4/30/23	5/31/23				368	1	x	y	z	Sales	19/05/2021
3				1/31/23	2/28/23	3/31/23	4/30/23	5/31/23	6/30/23				398	2	x	y	z	Technical Support Services	03/10/2022
4		Heads per department		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23				369	3	x	y	z	Technical Support Services	19/12/2014
5	1	Administration		0	0	0	0	0	0				382	4	x	y	z	Technical Support Services	11/04/2016
6	1	Chairman		0	0	0	0	0	0				277	5	x	y	z	Executive	17/03/2021
7	10	Commercial		0	0	0	0	0	0				424	6	x	y	z	Technical Support Services	12/12/2022
8	11	Compliance		0	0	0	0	0	0				406	7	x	y	z	Finance	13/02/2023
9	14	Contract		0	0	0	0	0	0				455	8	x	y	z	Commercial	24/05/2021
10	1	Executive		0	0	0	0	0	0				387	9	x	y	z	Sales	02/12/2013
11	4	Finance		0	0	0	0	0	0				420	10	x	y	z	Executive	03/04/2009
12	5	HR		0	0	0	0	0	0				328	11	x	y	z	Professional Services	01/03/2012
13	6	IT		0	0	0	0	0	0				465	12	x	y	z	Sales	28/11/2017
14	7	Marketing		0	0	0	0	0	0				96	13	x	y	z	Operations	09/06/2014
15	8	Operations		0	0	0	0	0	0				498	14	x	y	z	Telemarketing	21/03/2023
16	3	Pre-Sales		0	0	0	0	0	0				298	15	x	y	z	Service	08/07/2013
17	3	Professional Services		0	0	0	0	0	0				313	16	x	y	z	Service	04/03/2019
18	12	Project		0	0	0	0	0	0				485	17	x	y	z	Telemarketing	22/02/2023
19	12	Project Management		0	0	0	0	0	0				467	18	x	y	z	Project Management	10/05/2010
20	2	Sales		0	0	0	0	0	0				456	19	x	y	z	Professional Services	09/05/1994
21	2	Sales-Comms		0	0	0	0	0	0				442	20	x	y	z	Technical Support Services	28/06/2021
22	2	Sales-Print		0	0	0	0	0	0				327	21	x	y	z	Sales-Comms	13/09/2021
23	9	Service		0	0	0	0	0	0				482	22	x	y	z	Operations	26/01/2023
24	9	Service Delivery		0	0	0	0	0	0				153	23	x	y	z	Sales-Print	27/04/2016
25	8	Service Desk - Builds		0	0	0	0	0	0				319	24	x	y	z	Operations	09/03/2020
26	13	Technical Support Services		0	0	0	0	0	0				379	25	x	y	z	Technical Support Services	09/05/2022
27	2	Telemarketing		0	0	0	0	0	0				478	26	x	y	z	Technical Support Services	03/01/2023
28	2	Telesales		0	0	0	0	0	0				421	27	x	y	z	Professional Services	03/08/2020
29		TOTAL		-	-	-	-	-	-				463	28	x	y	z	Sales	02/07/2019
30													413	29	x	y	z	Professional Services	07/06/2021
31													318	30	x	y	z	Marketing	18/06/2007
32													345	31	x	y	z	Service	14/02/2022
33													273	32	x	y	z	Operations	16/03/2020
34													430	33	x	y	z	Technical Support Services	20/06/2011
35													1	34	x	y	z	Operations	24/04/2006
36													470	35	x	y	z	Sales	18/02/2008
37													393	36	x	y	z	Professional Services	01/06/2010
38													283	37	x	y	z	Executive	17/05/2021
39													472	38	x	y	z	Technical Support Services	11/04/2022
40													394	39	x	y	z	Professional Services	12/12/2022
41													488	40	x	y	z	Service	13/03/2023
42													414	41	x	y	z	Technical Support Services	08/10/2015
43													4	42	x	y	z	Commercial	01/04/2003
44													205	43	x	y	z	Finance	15/10/2018
KPIs

Cell Formulas
Range	Formula
E2:I2	E2	=D3
D5:I28	D5	=COUNTIFS($R:$R,KPIs!$B5,$S:$S,"<="&D$2,$T:$T,"")+COUNTIFS($R:$R,KPIs!$B5,$S:$S,"<="&D$2,Summary!$T:$T,">="&D$3)
D29:I29	D29	=SUM(D5:D28)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:M65,M67:M215	Cell Value	duplicates	text	NO

 

[Joe4]

Your formula is referencing two different sheets (SUMMARY and KPIs).
Without see the data on all the different sheets, it is tough to day.

But for comparing dates, make sure ALL your date fields are formatted as Dates and not as Text!
One easy way to tell is to expand the size of the column. If your value is left-justified, it is probably text.
In the following example, the first and 3rd dates are text, not date entries: