Same value in one column, then comparing by dates and names in two other columns

[FriendshipWaffles]

Hi all -- I am trying to compare the visits for each patient based on the date of service and role listed. More specifically, how many times did a specific physician have a visit with a patient and then refer them to an APC visit (using the date of service as the point of comparison). The Visit Identifier is always a unique value for a specific visit.

In the below screenshot, Dr. A would be counted twice as they saw Patient 1 and that patient had a subsequent APC visit the following month two different times. Dr. C would be counted once as they referred to an APC for Patient 3. However, Dr. A could not count for Patient 3 since their visit was after the APC visit so they did not complete the referral, etc etc. There are 10,000 rows of visits to compare -- some patients are only listed twice, others are listed a dozen times.

Hope that makes sense! I am really struggling so thank you to anyone who can help. (Sorry for the screenshot, work won't allow the Xl2bb add in and mini sheet)

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Patient			Provider		Doctor/Patient	1	2	3
2	1			Dr. A		Dr. A	2	0	0
3	1			APC		Dr. B	0	0	0
4	1			Dr. A		Dr. C	0	0	1
5	1			APC
6	1			APC
7	2			APC
8	2			APC
9	2			APC
10	2			APC
11	3			Dr. B
12	3			Dr. C
13	3			Dr. C
14	3			APC
15	3			Dr. A
Sheet6

Cell Formulas
Range	Formula
G2:I4	G2	=COUNTIFS($D$3:$D$15,"APC",$D$2:$D$14,F2:F4,$A$3:$A$15,G1:I1)
Dynamic array formulas.

 

[FriendshipWaffles]

Hi Cubist -- Thanks for the reply! I am getting a circular reference error and all cells in the new table have a value of 0 except for the very first one which has a 2. Any thoughts on what I might be doing wrong?

 

[Cubist]

Hi Cubist -- Thanks for the reply! I am getting a circular reference error and all cells in the new table have a value of 0 except for the very first one which has a 2. Any thoughts on what I might be doing wrong?

Hard to tell without seeing what you did.