Jun 04 2019 02:17 AM
Morning everyone
I'm using Office/Excel 2010 and have a long list of patients with a date they arrived on a ward and the date the left the ward; what i need is a count of all the people that are on the ward each day. In the excerpt below, I can see that on the 2nd of April there were 4 (325, 327, 330, 331) who were already on the ward and another 4 (332,333,334,335) who were admitted.
I've tried asking colleagues and Dr Google and had a go with the following formula which gives 9 for some reason:
=SUMPRODUCT((A2>$I$5:$I$537)*1,(A2<$J$5:$J$537)*1)
Ultimately, what I need is a count of people/patients on the ward from 01 Jan 2019 through to the end of April (I have these dates in Column L)
Any help and assistance is very much appreciated :)
Ian
Person # | Date In | Date out |
321 | 30/03/19 | 30/03/19 |
322 | 30/03/19 | 01/04/19 |
323 | 30/03/19 | 30/03/19 |
324 | 31/03/19 | 31/03/19 |
325 | 01/04/19 | 03/04/19 |
326 | 01/04/19 | 01/04/19 |
327 | 01/04/19 | 04/04/19 |
328 | 01/04/19 | 01/04/19 |
329 | 01/04/19 | 01/04/19 |
330 | 01/04/19 | 02/04/19 |
331 | 01/04/19 | 02/04/19 |
332 | 02/04/19 | 05/04/19 |
333 | 02/04/19 | 03/04/19 |
334 | 02/04/19 | 04/04/19 |
335 | 02/04/19 | 04/04/19 |
336 | 03/04/19 | 05/04/19 |
337 | 03/04/19 | 03/04/19 |
338 | 04/04/19 | 05/04/19 |
Jun 04 2019 03:01 AM
Solution@Ian_in_the_NHS , if in A2 is 02 Apr, when 331 and 332 do not meet your condition (date in J is more than A2). I guess it shall be "more or equal to" like
=SUMPRODUCT((A2>=$I$5:$I$537)*(A2<=$J$5:$J$537))
Jun 05 2019 05:30 AM
Many thanks for that, it works! It's amazing how much difference two little characters could make!
Brgds
Ian
Jun 05 2019 01:44 PM
Hi Ian, glad to help
Jun 04 2019 03:01 AM
Solution@Ian_in_the_NHS , if in A2 is 02 Apr, when 331 and 332 do not meet your condition (date in J is more than A2). I guess it shall be "more or equal to" like
=SUMPRODUCT((A2>=$I$5:$I$537)*(A2<=$J$5:$J$537))