+ Reply to Thread
Results 1 to 4 of 4

Advanced unique cell count with multiple conditions ... help!

  1. #1
    Registered User
    Join Date
    05-09-2004
    Posts
    2

    Unhappy Advanced unique cell count with multiple conditions ... help!

    I have spent 2 days now trying to get this to work, so any help would be much appreciated.

    I am trying to count the number of unique cells in a range, where the unique cells have a corresponding date that must fall within a particular period.
    eg, consider the following data: (csv format)
    #ColA,#ColB
    Jane Smith,17-Dec-03
    Luke Simons,21-Jun-04
    Nick James,16-Dec-03
    Bob Sampson,3-Jul-03
    Greg Thingh,28-Nov-03
    Kieran Smyth,23-Sep-04
    Michael Smith,27-Aug-04
    Michael Smith,30-Jul-04
    Jane Sheppard,4-Aug-04
    Borris Lee,19-Jul-05
    Simon Johns,6-Sep-05
    Alex Foote,12-Sep-05
    Chris Hander,21-Sep-05
    Jane Sheppard,24-Sep-05
    Georgia Se,24-Sep-05

    If I count all unique ColA cells that have a ColB date between 1-Sep-05 and 30-Sep-05 inclusive, I get 5. But how do I do this using excel worksheet formulas?? I have tried the following array formula (enter with CTRL+SHIFT+ENTER):
    =SUM(IF(FREQUENCY(A2:A16,A2:A16)>0,IF(B2:B16>=DATE(2005,1,1),IF(B2:B16<=DATE(2005,9,30),1))))
    The problem is, the above formula equates to 4.
    I think what is happening is the frequency filter returns a range of cells that are the first unique occurences. That is, just the unique filter on ColA returns 12, BUT the first occurence of Jane Sheppard is returned. This cell (A10 in this example) has a value 4-Aug-04 in B10 which does not satisfy the date conditions, hence the final sum is 4 and not 5.

    Please please, if someone could help me to write a worksheet that will count unique cells with conditions such as in my example, I would be ever so thankful!!! (no vba scripts please, worksheet formulas only)

  2. #2
    Aladin Akyurek
    Guest

    Re: Advanced unique cell count with multiple conditions ... help!


    Let A2:B16 house the sample you provided.

    Some options...

    If you have Longre's morefunc.xll add-in...

    =COUNTDIFF(IF($B$2:$B$16-DAY($B$2:$B$16)+1=D2,$A$2:$A$16,0),FALSE,0)

    which must be confirmed with control+shift+enter.

    Otherwise...

    Either:

    =SUMPRODUCT(--($A$2:$A$16<>""),--($B$2:$B$16-DAY($B$2:$B$16)+1=D2),--(MATCH($A$2:$A$16&$B$2:$B$16,$A$2:$A$16&$B$2:$B$16,0)=ROW($B$2:$B$16)-ROW($B$2)+1))

    Or:

    =SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<>""),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($B$2:$B$16)-ROW($B$2)+1)>0,1))


    Flystar wrote:
    > I have spent 2 days now trying to get this to work, so any help would be
    > much appreciated.
    >
    > I am trying to count the number of unique cells in a range, where the
    > unique cells have a corresponding date that must fall within a
    > particular period.
    > eg, consider the following data: (csv format)
    > #ColA,#ColB
    > Jane Smith,17-Dec-03
    > Luke Simons,21-Jun-04
    > Nick James,16-Dec-03
    > Bob Sampson,3-Jul-03
    > Greg Thingh,28-Nov-03
    > Kieran Smyth,23-Sep-04
    > Michael Smith,27-Aug-04
    > Michael Smith,30-Jul-04
    > Jane Sheppard,4-Aug-04
    > Borris Lee,19-Jul-05
    > Simon Johns,6-Sep-05
    > Alex Foote,12-Sep-05
    > Chris Hander,21-Sep-05
    > Jane Sheppard,24-Sep-05
    > Georgia Se,24-Sep-05
    >
    > If I count all unique ColA cells that have a ColB date between 1-Sep-05
    > and 30-Sep-05 inclusive, I get 5. But how do I do this using excel
    > worksheet formulas?? I have tried the following array formula (enter
    > with CTRL+SHIFT+ENTER):
    > =SUM(IF(FREQUENCY(A2:A16,A2:A16)>0,IF(B2:B16>=DATE(2005,1,1),IF(B2:B16<=DATE(2005,9,30),1))))
    > The problem is, the above formula equates to 4.
    > I think what is happening is the frequency filter returns a range of
    > cells that are the first unique occurences. That is, just the unique
    > filter on ColA returns 12, BUT the first occurence of Jane Sheppard is
    > returned. This cell (A10 in this example) has a value 4-Aug-04 in B10
    > which does not satisfy the date conditions, hence the final sum is 4
    > and not 5.
    >
    > Please please, if someone could help me to write a worksheet that will
    > count unique cells with conditions such as in my example, I would be
    > ever so thankful!!! (no vba scripts please, worksheet formulas only)
    >
    >


  3. #3
    Aladin Akyurek
    Guest

    Re: Advanced unique cell count with multiple conditions ... help!

    BTW, D2 houses a first day date like 1-Sep-05.

    Aladin Akyurek wrote:
    >
    > Let A2:B16 house the sample you provided.
    >
    > Some options...
    >
    > If you have Longre's morefunc.xll add-in...
    >
    > =COUNTDIFF(IF($B$2:$B$16-DAY($B$2:$B$16)+1=D2,$A$2:$A$16,0),FALSE,0)
    >
    > which must be confirmed with control+shift+enter.
    >
    > Otherwise...
    >
    > Either:
    >
    > =SUMPRODUCT(--($A$2:$A$16<>""),--($B$2:$B$16-DAY($B$2:$B$16)+1=D2),--(MATCH($A$2:$A$16&$B$2:$B$16,$A$2:$A$16&$B$2:$B$16,0)=ROW($B$2:$B$16)-ROW($B$2)+1))
    >
    >
    > Or:
    >
    > =SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<>""),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($B$2:$B$16)-ROW($B$2)+1)>0,1))
    >
    >

    [...]

  4. #4
    Registered User
    Join Date
    05-09-2004
    Posts
    2
    Thanks Aladin, your options work wonderfully. For the benefit of others, the final formula I chose to use was:
    =SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<>""),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($B$2:$B$16))>0,1))

+ 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