HELP with COUNTIFS :( I have a single hair left, please save it

[RichardX720]

I have an excel document that I created on OSX Excel 2016. I no longer have a Mac and now using Windows Excel 2019. I opened the sheet and added more content for it to calculate and everything went to 0's...

I cannot figure it out why.. I only pasted a few lines of VData below but you get the example.. Its USED to count how many times "UserName 1" showed up in Column C of VData inbetween the dates mentioned checked against Colum D of VData. The cell dates are dd/mm/yyyy and I am using US Region but I have tried to change region to NL and still doesnt work... PLEASE HELP!!

2019-2020_USERS_TESTING.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Doctor	2020	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	UserName 1	0	0	0	0	0	0	0	0	0	0	0	0	0
3	UserName 2	0	0	0	0	0	0	0	0	0	0	0	0	0
4	UserName 3	0	0	0	0	0	0	0	0	0	0	0	0	0
5	UserName 4	0	0	0	0	0	0	0	0	0	0	0	0	0
6	UserName 5	0	0	0	0	0	0	0	0	0	0	0	0	0
7	UserName 6	0	0	0	0	0	0	0	0	0	0	0	0	0
8	UserName 7	0	0	0	0	0	0	0	0	0	0	0	0	0
9	UserName 8	0	0	0	0	0	0	0	0	0	0	0	0	0
10	UserName 9	0	0	0	0	0	0	0	0	0	0	0	0	0
11	UserName 10	0	0	0	0	0	0	0	0	0	0	0	0	0
12	UserName 11	0	0	0	0	0	0	0	0	0	0	0	0	0
13	UserName 12	0	0	0	0	0	0	0	0	0	0	0	0	0
14	UserName 13	0	0	0	0	0	0	0	0	0	0	0	0	0
15	UserName 14	0	0	0	0	0	0	0	0	0	0	0	0	0
16	UserName 15	0	0	0	0	0	0	0	0	0	0	0	0	0
17	UserName 16	0	0	0	0	0	0	0	0	0	0	0	0	0
18	UserName 17	0	0	0	0	0	0	0	0	0	0	0	0	0
19	Totals	0	0	0	0	0	0	0	0	0	0	0	0	0
CONSULTS

Cell Formulas
Range	Formula
B2:B18	B2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/12/2020")
C2:C18	C2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/01/2020")
D2:D18	D2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/02/2020",VData!D:D,"<="&"28/02/2020")
E2:E18	E2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/03/2020",VData!D:D,"<="&"31/03/2020")
F2:F18	F2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/04/2020",VData!D:D,"<="&"30/04/2020")
G2:G18	G2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/05/2020",VData!D:D,"<="&"31/05/2020")
H2:H18	H2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/06/2020",VData!D:D,"<="&"30/06/2020")
I2:I18	I2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/07/2020",VData!D:D,"<="&"31/07/2020")
J2:J18	J2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/08/2020",VData!D:D,"<="&"31/08/2020")
K2:K18	K2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/09/2020",VData!D:D,"<="&"30/09/2020")
L2:L18	L2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/10/2020",VData!D:D,"<="&"31/10/2020")
M2:M18	M2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/11/2020",VData!D:D,"<="&"30/11/2020")
N2:N18	N2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/12/2020",VData!D:D,"<="&"31/12/2020")
B19	B19	=SUM([2020])
C19	C19	=SUM([JAN])
D19	D19	=SUM([FEB])
E19	E19	=SUM([MAR])
F19	F19	=SUM([APR])
G19	G19	=SUM([MAY])
H19	H19	=SUM([JUN])
I19	I19	=SUM([JUL])
J19	J19	=SUM([AUG])
K19	K19	=SUM([SEP])
L19	L19	=SUM([OCT])
M19	M19	=SUM([NOV])
N19	N19	=SUM([DEC])

2019-2020_USERS_TESTING.xls
	A	B	C	D	E
13	24	Consult	UserName 1	02/01/2019
14	24	Consult	UserName 12	02/01/2019
15	761	Consult	Username 2	02/01/2019
16	3018	Consult	Username 14	02/01/2019
17	3018	Consult	Username 3	02/01/2019
18	3018	Consult	Username 9	02/01/2019
VData

 

[RoryA]

I assume you actually have some 2020 dates in the file?

 

[RichardX720]

haha YES plenty..

my mistake for only grabbing a block from 2019 and only showing the table for 2020.. but yet there are 1000's of 2020's

 

[RichardX720]

SHCF-2019-2020_Doctor-Consults_TESTING.xls
	A	B	C	D	E
14283	426	Consult	UserName 1	01/06/2020
14284	4461	Consult	Username 12	01/06/2020
14285	493	Consult	Username 1	01/06/2020
14286	4090	Consult	Username 3	01/06/2020
VData

 

[Fluff]

Are you sure those are dates & not text? If you change the format of the whole column to General, what do you see?

 

[Toadstool]

Hi RichardX720,
Your data is in US m/d/yyyy format but your search is in dd/mm/yy format.

 

[RoryA]

Any circular reference error messages in the status bar?

 

[RichardX720]

Hi RichardX720,
Your data is in US m/d/yyyy format but your search is in dd/mm/yy format.

no its actually not, I just snapped shots of the lower months so its hard to tell..

 

[RichardX720]

Are you sure those are dates & not text? If you change the format of the whole column to General, what do you see?

How it looks now if i select the cell..

How it looks when I change it to GENERAL

 

[RichardX720]

Any circular reference error messages in the status bar?

I dont see any errors in the status bar at all