I work in a drug testing program and we perform drug tests on individuals in
the program as well as random drug tests of people not in the program, all of
which are logged on a single spreadsheet. The program has a thirty day
duration, though individuals who had been on the program, but have expired
may still have to do random drug testing. We list, in different workbooks,
the people who are on the program and the people who have finished the
program (with the date finished). I am trying to create a formula that will
count the number of tests performed just on those individuals on the program
(which will include those tests performed on individuals who's term on the
program had started or expired during the month, but only the ones after they
started or before they completed the program).

Examples:

[Worksheet: Program Master List]
ID# Date Started
22 1/1/2006
44 1/1/2006
55 1/18/2006
66 1/24/2006

[Worksheet: Program Expired List]
ID # Date Ended
11 1/18/2006
33 1/23/2006

[Worksheet: January Tests]
ID # Date Tested
11 1/6/2006
22 1/8/2006
55 1/9/2006
44 1/14/2006
33 1/16/2006
11 1/21/2006
55 1/21/2006
66 1/25/2006

Any help would be appreciated.

Hi Chris,

Would you like to count, for example, how many tests ID# 22 have gone
through, which is 2. So where would you like to have this formula. The
formula would be like this

=SUM(COUNTIF('Program Master List'!B1:B3,22),COUNTIF('Program Expired
List'!B1:B3,22),COUNTIF('January Tests'!B1:B3,22))

I know, someone will give you a better formula. I too will be looking
for a better one.

Hope that helps.

Thankyou,

Shail



Chris wrote:
I work in a drug testing program and we perform drug tests on individuals in
the program as well as random drug tests of people not in the program, all of
which are logged on a single spreadsheet. The program has a thirty day
duration, though individuals who had been on the program, but have expired
may still have to do random drug testing. We list, in different workbooks,
the people who are on the program and the people who have finished the
program (with the date finished). I am trying to create a formula that will
count the number of tests performed just on those individuals on the program
(which will include those tests performed on individuals who's term on the
program had started or expired during the month, but only the ones after they
started or before they completed the program).

Examples:

[Worksheet: Program Master List]
ID# Date Started
22 1/1/2006
44 1/1/2006
55 1/18/2006
66 1/24/2006

[Worksheet: Program Expired List]
ID # Date Ended
11 1/18/2006
33 1/23/2006

[Worksheet: January Tests]
ID # Date Tested
11 1/6/2006
22 1/8/2006
55 1/9/2006
44 1/14/2006
33 1/16/2006
11 1/21/2006
55 1/21/2006
66 1/25/2006

Any help would be appreciated.