• 546K Members
• 5,465 Online
• 651K Conversations
SOLVED

Highlighted
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

Highlighted

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

Related Conversations
SharePoint Lists - Expanded View Roadmap ID: 57302
Kotus-Tech in SharePoint on
9 Replies
Multi-value fields display no separator
Florian Hein in SharePoint on
1 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
Date filtering of Bing Searches
PhilipE3 in Discussions on
2 Replies
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies