# Count using complex criteria

1. ## 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:

2. ## Re: Count using complex criteria

"Rob" <anonymous@discussions.microsoft.com>ha scritto nel messaggio
O\$QAvtHUFHA.2664@TK2MSFTNGP15.phx.gbl

> 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:D13>0,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.
--

Regards

Franz

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

3. ## Re: Count using complex criteria

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

"Franz" <fra68ve@InVento.it> wrote in message
news:ufqw9hJUFHA.2472@TK2MSFTNGP10.phx.gbl...
> "Rob" <anonymous@discussions.microsoft.com>ha scritto nel messaggio
> O\$QAvtHUFHA.2664@TK2MSFTNGP15.phx.gbl
>
>> 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:D13>0,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.
> --
>
> Regards
>
> Franz
>
> ----------------------------------------------------------------------------------------
> To reply translate from italian InVento (no capital letters)
> ----------------------------------------------------------------------------------------
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1