2 problems (breaking link with other doc + COUNTIFS formula does not count everything)

[Test_Database_2021]

Hi everyone,

So I have two questions today.

Number 1:
I accidentally kept some kind of link to another file (I only copy-pasted values).
What I now want to do is break the connection. But if I do this (see pic), clicking on "break links", it does not do anything. I keep getting the warning repeatedly.

Number 2:
The formula also shown in the pic is only doing a partial COUNTIFS (the result it gives is far too small and does not match at all what I get when filtering the data manually).
=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Z:$Z,B14)

Reasons I can think of and already checked:
- The B14 cell does not match what is in the source column Z of the other sheet. --> already checked and corrected, yes it does and there is only this spelling, no spaces
- There is some kind of unnecessary extra criterion that restricts the count --> already checked, it is a simple formula so easy to do just these filters
- There is a problem with the file --> I copy/pasted everything into a new file that is smaller, did just this calculation, and got the same problem.

Can anyone think of anything else I can do? It's a binary Excel file of 3,299KB and I only have Excel 2010 with no extra licenses. Thanks!

 

[Sufiyan97]

Post you data using below link

 

[Test_Database_2021]

Can't post confidential data, sorry.
Do you think the file is too big? It doesn't show that the calculations are too slow or anything like this.

 

[Sufiyan97]

Sorry actually I did not understand what is happening with image only

 

[Test_Database_2021]

Sorry actually I did not understand what is happening with image only

The Cells in red show the wrong result:

20210416_Liberia_Cohort_Analysis.xlsb
	A	B	C	D	E	F
9	Mental Health - cumulative:
10		Reason for discharge - nb.:		%	% <=2 consultations:	Av. no. of weeks in care:
11		Discharge: With patient's agreement - end of care	441	24%	28%	33.3
12		Discharge: With patient's agreement - patient moved	102	6%
13	N discharged:	Discharge: With patient's agreement - patient is referred	70	4%
14	1807	Defaulter: No information from patient	468	26%
15		Defaulter: Dissatisfied / different expectation of service	11	1%
16	All have reason recorded.	Deceased	22	1%
17
18	Mental Health - cumulative:
19	N discharged:	Improvement (CGI-I):	Nb.:	%:
20	1807	Improved (1,2,3)	405	22%
21		No change (4)	48	3%
22	N discharged with outcome:	Worsening of symptoms (5,6,7)	6	0%
23	1219	Unknown	588	33%
24
25	2. Treatment Outcomes by Year of Admission ("what happened to the patients, regardless of whether their files were closed?"):
26	This is just to report the evolution across the years, e.g. the percentage of patients still active from each year.
27
28	Mental Health:
29	Year of admission:	Reason for discharge - nb.:		%	% <=2 consultations:	Av. no. of weeks in care:
30	2017	Discharge: With patient's agreement - end of care	106	12%	42%
31		Discharge: With patient's agreement - patient moved	18	2%
32	N admitted:	Discharge: With patient's agreement - patient is referred	21	2%
33	874	Defaulter: No information from patient	33	4%
34		Defaulter: Dissatisfied / different expectation of service	1	0%
35		Deceased	5	1%
36		Care ongoing	46	5%
Treatment Outcomes

Cell Formulas
Range	Formula
C11:C16	C11	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Z:$Z,B11)
D11:D16	D11	=C11/$A$14
E11	E11	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!O9,Patients!$Y:$Y,"<>",Patients!$AN:$AN,"<=2")/COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!O9)
F11	F11	=AVERAGEIFS(Patients!$AS:$AS,Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Y:$Y,"<>")
A14,A20	A14	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Y:$Y,"<>")
C20	C20	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,"<=3")
D20:D23	D20	=C20/$A$20
C21	C21	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,"4")
C22	C22	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,">=5")
C23	C23	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,"",Patients!$Y:$Y,"<>")
A23	A23	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Y:$Y,"<>",Patients!$AB:$AB,"<>")
C30:C35	C30	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$B:$B,"<=12/31/2017",Patients!$Z:$Z,B30)
D30:D35	D30	=C30/$A$33
C36	C36	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$B:$B,"<=12/31/2017",Patients!$Y:$Y,"")
D36	D36	=C36/A33
E30	E30	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!O9,Patients!$B:$B,"<=12/31/2017",Patients!$Y:$Y,"<>",Patients!$AN:$AN,"<=2")/COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!O9,Patients!$B:$B,"<=12/31/2017")
A33	A33	=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!O10,Patients!$B:$B,"<=12/31/2017")