Count Number of Patient's present during an hour Episode

[whamps11]

Hello,

I use excel common formulas often but am a neophyte when it comes to more complex formulas. I am working on a Recovery Room staffing project for a Director of Nursing. She wants to know the the number of patients in her rocovery room at hour intervals. I want to be able to count that between 8:00-8:59 there were x number of patients in Recovery and so on etc for the different hour intervals for a day. I have attached a sample spreadsheet. Feel free to change around what ever you want. Can you also explain what the parts of your formula are doing so I can learn how to do it for other projects?

Thanks in advance for you help, I love this forum!

Jason

[Palmetto]

The easiest solution is to use a Pivot Table and group the time values by hour.

Also, you could use this SUMPRODUCT formula.

=SUMPRODUCT(--($B$2:$B$8>=E2)*--($B$2:$B$8<=F2))

See this page for explanation on how the SUMPRODUCT function works.

Sumproduct Explained

[bentleybob]

Alternatively, use COUNTIFS, which count cells in a range that meet certain criteria. Here, I've tested for ones starting within the hour plus those ending in the hour, minus those both starting and ending in the hour (to avoid double-counting). Same result as SUMPRODUCT but maybe easier to follow.

[whamps11]

Palmetto and Bentley bob thanks for the reply. Bently bob I think yours fits a little more because it counts the patients that are in the recovery room during each hour which includes double counting each patient if they are in two different hours. Palmetto yours just counts the hour. I do thank both of you for the help. I learned something from each one of your posts thanks for the help!!

Whamps

[whamps11]

Bentley bob I have one more question. I found a snag in the formulas. If the recovery period extended over three hours 8:00 - 10:45 it only counts 8:00 and the 10:00 hour skipping over 9:00 would you know how to fix this? See attached

Thanks
Jason

[bentleybob]

My bad. Add the following to cell I3 and copy it down.

+COUNTIFS($B:$B,"<"&$G3,$C:$C,">"&$H3)

[Jomejorada]

Hello, I tried to use the formula you used for Jason's project but it's giving me negative results.

Thank you for the help!

[arlu1201]

Jomejorada,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.