Hi all,
I'm new here, so I hope I'm following all the rules! I've tried searching to see if anyone has had this problem, but I haven't come up with anything that fixes my problem.
I'm not a particularly advanced Excel user. I used it in a job a long time ago, but now I'm a nurse, however I'm the techiest person in my team, so I get these jobs!
We are running some studies, and I need to track the number of people we recruit each month, to each separate study.
We record all participants on a spreadsheet in different tabs, and for each study I make sure that they have a unique study ID and a date of recruitment.
I then have a separate tab to track these.
I set it up for January, then drag it across (making sure I've put $ in the right places) so it just carries on doing the same thing all year.
Here it is:
=COUNTIFS('PETRA Screening log'!$H$2:$H$1048576,">="&Tracker!S$3,'PETRA Screening log'!$H$2:$H$1048576,"<="&Tracker!S$4,'PETRA Screening log'!$G$2:$G$1048576,"<>"&"")
Where Petra screening log is the tab with all the participants on. Column H has the date recruited, column G is their unique ID (so I just need that to not be empty), and S3 and S4 are the dates I want it to count inbetween.
It has worked all year - but this month it counts 21 when there are only 20. And I can't for the life of my figure out why!
I've checked that there are no hidden rows.
I've checked the formatting on the column with the dates and they are all formatted as dates. I've checked that there are no extra numbers hidden anywhere.
I'm pretty certain that I will have done something silly - but if someone could tell me what it might be, I'd be really grateful.
Thanks you so much
Rachel
I'm new here, so I hope I'm following all the rules! I've tried searching to see if anyone has had this problem, but I haven't come up with anything that fixes my problem.
I'm not a particularly advanced Excel user. I used it in a job a long time ago, but now I'm a nurse, however I'm the techiest person in my team, so I get these jobs!
We are running some studies, and I need to track the number of people we recruit each month, to each separate study.
We record all participants on a spreadsheet in different tabs, and for each study I make sure that they have a unique study ID and a date of recruitment.
I then have a separate tab to track these.
I set it up for January, then drag it across (making sure I've put $ in the right places) so it just carries on doing the same thing all year.
Here it is:
=COUNTIFS('PETRA Screening log'!$H$2:$H$1048576,">="&Tracker!S$3,'PETRA Screening log'!$H$2:$H$1048576,"<="&Tracker!S$4,'PETRA Screening log'!$G$2:$G$1048576,"<>"&"")
Where Petra screening log is the tab with all the participants on. Column H has the date recruited, column G is their unique ID (so I just need that to not be empty), and S3 and S4 are the dates I want it to count inbetween.
It has worked all year - but this month it counts 21 when there are only 20. And I can't for the life of my figure out why!
I've checked that there are no hidden rows.
I've checked the formatting on the column with the dates and they are all formatted as dates. I've checked that there are no extra numbers hidden anywhere.
I'm pretty certain that I will have done something silly - but if someone could tell me what it might be, I'd be really grateful.
Thanks you so much
Rachel