• 504K Members
• 2,177 Online
• 602K Conversations
SOLVED

New Contributor

# Count of people on a ward for a list of dates

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

3 Replies
Highlighted
Solution

# Re: Count of people on a ward for a list of dates

@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))`

# Re: Count of people on a ward for a list of dates

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

Brgds

Ian

# Re: Count of people on a ward for a list of dates

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies