Count of Account Numbers

[doblerra]

Hi all,

I am trying to find the length of stay for patients moving within a hospital. See attached spreadsheet - For example, patient X123 makes a move to 2 units within the hospital. How do I calculate what their length of stay is for the combined moves? (It should be 8 minutes for this particular patient)

Thanks!

[benishiryo]

welcome to the forum, doblerra. what do you want to fill in under Column E? the accumulative time? only the total for the last time? for the former:
=MAX(IF(A$2:A2=A2,C$2:C2))-MIN(IF(A$2:A2=A2,B$2:B2))

for the latter:
=IF(COUNTIF(A$2:A2,A2)<>COUNTIF($A$2:$A$7,A2),"",MAX(IF(A$2:A2=A2,C$2:C2))-MIN(IF(A$2:A2=A2,B$2:B2)))

both formulas are array formulas
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

for simplicity, you can also just do this in E2:
=C2-B2

then do a Pivot Table

[jetermaw9]

Easiest way is to consolidate your list of patients using a "remove duplicates," then do a sumif of the times - I've attached for your sample