Hi,

Hopefully the table below shows, copied from Excel.

I need to count the number of live contracts at a given date, a contractor
can have several contract entries, for example Jones (459) had a contract
from 10/02/99 to 12/05/04 but also renewed his contract from the 13/05/04,
for Jones I would want to count him once as he was live at the month end:
30/04/05. Joyce (850) has 3 entries and his termination date is after the
month end of 30/04/05 so again I would count him once from the second entry,
the third entry of Joyce is a future renewal which starts after the month
end so wouldn't be counted.

Can this be achieved with a single formula?

Thanks, Rob


Contract Name CommDate TermDate
459 Jones 10/02/1999 12/05/2004
459 Jones 13/05/2004
850 Joyce 15/08/1996 11/04/2004
850 Joyce 12/04/2004 11/05/2005
850 Joyce 12/05/2005
465 Markham 15/09/1987 12/10/1999
465 Markham 13/10/1999
461 Peters 29/05/2005
458 Smith 21/09/1996 10/02/2001
458 Smith 11/02/2001 21/05/2003
458 Smith 22/05/2003
745 Smith 25/08/1999 28/05/2005

Start date: 27/03/2005
End date: 30/04/2005

Live contracts:
Tern contracts: