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

Test_Database_2021

New Member
Joined
Apr 6, 2021
Messages
9
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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.

1618563011674.png


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!
 

Attachments

  • 1618562951831.png
    1618562951831.png
    213.2 KB · Views: 3

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
291
Office Version
  1. 2013
Platform
  1. Windows
Post you data using below link
 

Test_Database_2021

New Member
Joined
Apr 6, 2021
Messages
9
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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.
 

Test_Database_2021

New Member
Joined
Apr 6, 2021
Messages
9
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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
ABCDEF
9Mental Health - cumulative:
10Reason for discharge - nb.:%% <=2 consultations:Av. no. of weeks in care:
11Discharge: With patient's agreement - end of care44124%28%33.3
12Discharge: With patient's agreement - patient moved1026%
13N discharged:Discharge: With patient's agreement - patient is referred704%
141807Defaulter: No information from patient46826%
15Defaulter: Dissatisfied / different expectation of service111%
16All have reason recorded.Deceased221%
17
18Mental Health - cumulative:
19N discharged:Improvement (CGI-I):Nb.:%:
201807Improved (1,2,3)40522%
21No change (4)483%
22N discharged with outcome:Worsening of symptoms (5,6,7)60%
231219Unknown 58833%
24
252. Treatment Outcomes by Year of Admission ("what happened to the patients, regardless of whether their files were closed?"):
26This is just to report the evolution across the years, e.g. the percentage of patients still active from each year.
27
28Mental Health:
29Year of admission:Reason for discharge - nb.:%% <=2 consultations:Av. no. of weeks in care:
302017Discharge: With patient's agreement - end of care10612%42%
31Discharge: With patient's agreement - patient moved182%
32N admitted:Discharge: With patient's agreement - patient is referred212%
33874Defaulter: No information from patient334%
34Defaulter: Dissatisfied / different expectation of service10%
35Deceased51%
36Care ongoing465%
Treatment Outcomes
Cell Formulas
RangeFormula
C11:C16C11=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Z:$Z,B11)
D11:D16D11=C11/$A$14
E11E11=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)
F11F11=AVERAGEIFS(Patients!$AS:$AS,Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Y:$Y,"<>")
A14,A20A14=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Y:$Y,"<>")
C20C20=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,"<=3")
D20:D23D20=C20/$A$20
C21C21=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,"4")
C22C22=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,">=5")
C23C23=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$AB:$AB,"",Patients!$Y:$Y,"<>")
A23A23=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Y:$Y,"<>",Patients!$AB:$AB,"<>")
C30:C35C30=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$B:$B,"<=12/31/2017",Patients!$Z:$Z,B30)
D30:D35D30=C30/$A$33
C36C36=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$B:$B,"<=12/31/2017",Patients!$Y:$Y,"")
D36D36=C36/A33
E30E30=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")
A33A33=COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!O10,Patients!$B:$B,"<=12/31/2017")
 

Watch MrExcel Video

Forum statistics

Threads
1,132,973
Messages
5,656,174
Members
418,284
Latest member
LaurentT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top