Count of people on a ward for a list of dates

[Ian_in_the_NHS]

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

[#2 · Accepted 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))

[#3]

Hi @Sergei Baklan 

Many thanks for that, it works!  It's amazing how much difference two little characters could make!

Brgds

Ian

[#4]

@Ian_in_the_NHS 

Hi Ian, glad to help