+ Reply to Thread
Results 1 to 3 of 3

Count using complex criteria

  1. #1
    Rob
    Guest

    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. #2
    Franz
    Guest

    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.
    --
    Hoping to be helpful...

    Regards

    Franz

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



  3. #3
    Rob
    Guest

    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.
    > --
    > Hoping to be helpful...
    >
    > Regards
    >
    > Franz
    >
    > ----------------------------------------------------------------------------------------
    > To reply translate from italian InVento (no capital letters)
    > ----------------------------------------------------------------------------------------
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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