total and average patient counts through admit/discharge dates

If I understand correctly, I believe the first formula you want might be:

=SUMPRODUCT((A$2:A$500<EDATE(D2,1))*(A$2:A$500<>"")*((B$2:B$500>=D2)+(B$2:B$500="")))

because I assume if the Discharge date is blank, that you want to count that person in every month >= their Admit date.
This part of the formula: (A$2:A$500<>"") was only added if your range is greater than the list of dates. You don't need that part if your range is the same as the list of dates (or better yet, but the dates in an Excel Table).

And a guess for your average. in cell G2 and copied down:

=SUM(MAP($A$2:$A$308,$B$2:$B$308,LAMBDA(x,y,MAX(MIN(y,EOMONTH(E2,0))-MAX(x,E2)+1,0))))/DAY(EOMONTH(E2,0))

Oh great. After working on this, I now see you are on version 2016. Sorry, this won't work for you. I don't know how you'd do it with 2016.

Originally Posted by Gregb11

If I understand correctly, I believe the first formula you want might be:

=SUMPRODUCT((A$2:A$500<EDATE(D2,1))*(A$2:A$500<>"")*((B$2:B$500>=D2)+(B$2:B$500="")))

because I assume if the Discharge date is blank, that you want to count that person in every month >= their Admit date.
This part of the formula: (A$2:A$500<>"") was only added if your range is greater than the list of dates. You don't need that part if your range is the same as the list of dates (or better yet, but the dates in an Excel Table).

This formula works for me, thank you! I did adjust the A$500 to A$909, as I technically have 909 lines at this time, but it will slowly add more. If you have thoughts on how to do the average with excel 2016, I would appreciate it.

Originally Posted by Glenn Kennedy

I really do not follow what you want for the average...

I mean to say that for an individual who only occupies a bed for 15 out of a 30 day month (such as April), I want to count that as a 0.5 bed, as that bed was only occupied for half of the month. In a 100 bed facility, I want to know how many unique patients we treated (total count), as well as overall what percentage we kept all beds filled (average count). This is to help me track, as I see us brining in many patients, but I'm concerned they are not staying for as long as they should/need.

Glenn Kennedy, your formula did work for most of the months, but did not take into account clients who do not yet have a discharge date (it's blank as they are still in my system). To be fair, I forgot to include some of those in my sample spreadsheet.

Thank you

Hi Glenn,
Thank you for your assistance. Is it possible your formula does not work for me because I'm using an older version of Excel (2016)? When I download the spreadsheet you attached, it still does not work for me. At first all the numbers appear accurate, but as soon as I edit anything then the formula no longer works correctly. For example, the "average" column all reads as 100 now.

All that said, I do appreciate you helping me out. Thank you

@Glenn,

Delete the values in RED, to indicate that the patients are still there. The value in E25 alters to 4. So what's wrong with that?

I may have misinterpreted it, but that's where I thought the issue was. If there is no discharge date, they SHOULD be counted as being there. So if you delete the discharge date in your example, the number shouldn't change because their still considered there.

Originally Posted by Glenn Kennedy

If not, try entering IT as an array formula. You very quickly forget which formulae need to be entered as arrays, once you switch to O365.

That seems to be an IT issue for me, no array formula are working on this computer. It appears for now I'm really stuck. Thank you for the feedback thus far, I'll see if I can figure out the excel issue when my IT admin comes back from vacation next week.