Count using complex criteria
 

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:

"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)
----------------------------------------------------------------------------------------

Thanks very much Franz, that has got me started, with a few tweaks I'll be
there - I hope!

"Franz" wrote in message
...
"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)
----------------------------------------------------------------------------------------