countif column range equals any other cell in same range

Hi all,

I'm trying to count the number of re-admissions within a certain time frame (let's say no more than 60 days apart) by sorting data by patient id# then discharge date and counting the number of repeat values in this column that occur within this time frame. I was thinking a COUNTIFS would do the trick but do not know how to tackle the issue of writing a criteria expression for any cell in a cell range equaling any other cell in that same range. Basically I need the number of occasions where an ID# is reoccurs inside the time window.. So an ID# that shows up 9 times but only 4 instances of closely spaced consecutive service would count as 4. Also, a count of unique patient ID#s that fulfill the re-admission criteria would be useful..

I suppose this is a common manipulation in business terms as "repeat customers"..
Any help would be much appreciated. Thanks!

Hi pacer31,

I created some fake data to show how I might tackle this problem. The first in Col C is a counter of all IDs that are the same above the current rows ID. Then I created in D an Index so I could calculate how many days this ID happened since the first ID showing. I hope you can use this example and expand it to fit your needs.

see attached.

Hi pacer31,

I need a few more facts on this mythical problem.

You have patients who come in for appointments. You need to track how these are tied together. It sounds like a billing question to me. Something like the Patient has a car accident and you get to bill the insurance for 90 days of physical therapy tagged to the car accident. Then if they fall and hurt themselves and need therapy they need to change the reason or billing for the new problem.

I guess I need more specifics on how you currently track how an appointment is tied to a previous one.
A sample workbook would also help. You can attach a sample by clicking on "Go Advanced" and then the Paper Clip Icon above the message area.

Hi pacer,

I don't know what you have in column Q or R as I don't have a sample of what you are looking at. If you can attach a sample workbook, we might get farther with an answer we both understand.

OK - I'm on a dumb day.

What do you want in the green area of your attached? # Patient... ?? Is that the Number of Patients or the Number of the Patients??

Hi pacer31,

I suspect you are going to need another column of data to specify if the date was an Admission or a Visit. It seems your answer of 3 in cell N6 doesn't give you what you want. I was thinking it was the Number of Patients who had been readmitted in the last 30 days.

You will need to sort by date to run the counter column formulas in Column E. That was the key to my answer in the last attached file.

See if that doesn't give you the answer you need.

GREAT!

Now edit the first post in this thread and change the Prefix of the Title to Solved. This helps us know who is still looking for answers and who is not.

I see you are ahead of me on this - Good Job a second time. Glad you got an answer. This was a little tougher than the normal problem.