Matching Data

[docterryt]

I have a spreadsheet with one column lists the Physicians' names, while other columns lists their patients' names and the date the patient was seen. My problem........I have to count how many appts each doctor had seen during a certain quarter of the year. I may have one doctor having 250 yearly individual patient appts involving 35 patients but only 75 individual appts patients involving 15 patients in a given quarter. How do I find each Doctor's name and match his name to his patients during a specific quarter of the year.

Ultimately, I need a way to count how many individual patients appts and the number of patients seen during a specific period of time by referencing 3 columns of data.

 

[Fergus]

How do I just count the number of doctors and patience seen during the quarters.

Thta's a bit more complex, but see below:

DocTerry.xls
	A	B	C	D	E	F	G	H	I
1	Doctor	Patient	ApptDate		Doctor	Q1	Q2	Q3	Q4
2	Smith	Jones	1/1/06		From:	1/1/06	1/4/06	1/7/06	1/10/06
3	Smith	Jones	15/1/06		To:	31/3/06	30/6/06	30/9/06	31/12/06
4	Davis	James	22/1/06		Smith	2	2	1	2
5	Toms	Dickson	28/1/06		Davis	1	0	0	1
6	Smith	Fred	5/4/06		Toms	1	0	1	0
7	Dolittle	Jones	12/5/06		Docs	3	3	3	4
8	Smith	Fred	25/6/06		Patients	4	3	4	5
9	Who	Mathew	13/7/06
10	Smith	Mark	19/8/06
11	Toms	Luke	9/9/06
12	Who	John	21/9/06
13	Dolittle	Gandalf	10/10/06
14	Davis	Frodo	25/11/06
15	Who	Bolbo	25/12/06
16	Smith	Gandalf	26/12/06
17	Smith	Saruman	31/12/06
Sheet1

Formula in F7, copied across is : =COUNTDIFF(INDIRECT("A"&MATCH(F2,$C:$C,1)&":A"&MATCH(F3,$C:$C,1))) For which you need to have installed the Morefunc Add-in.
Formula in F8, copied across is : =SUMPRODUCT(--($C$2:$C$1000>=F$2),--($C$2:$C$1000<=F$3),--($B$2:$B$1000<>""))

PS. For this to work properly the appointments must be in chronological order.

 

[docterryt]

Thanks a lot. I will see how it works and let you know.

Cheeers,

Terry

 

[docterryt]

The code on F8 works great. I can't get the code, F7, that counts the doctors to work. It returns N/A for all situations.

 

[Fergus]

To use the function COUNTDIFF you need to install and activate the Add-in MoreFunc which you can download here: http://xcell05.free.fr/english/index.html