"Rob" ha scritto nel messaggio


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:


Try this formula:

=SUM((C2:C13<=$H$2)*(IF(D2:D130,D2:D13=$H$3,1)))

(array entered, i.e. with ctrl+shift+ enter, instead of enter)

The hypothesis is that your table is in the range A1:D13, with headers in
the first row, so in C2:C13 you have start dates and in D2:D13 you have end
dates.
--
Hoping to be helpful...

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------