Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    count unique dates

    I have a column with dates (A col) various people (B col) did tasks.
    Sometimes a person does a task multiple times -- could be twice in 1 day and once on three different other days. I want to be able to count that this person did the task FOUR times (four unique dates).

    I don't want to use a filter or pivot table. Is there a formula that could calculate this for each person found in the B column?

    I would have a list of the unique people on another sheet and want the corresponding count next to their name.

    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a sample file showing your setup?

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    sample.

    Here's a sample.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 980 Times in 910 Posts
    I can't see how you can do it as a formula, but a macro would work. You then use a formula to call the macro and return the result, or run the macro and place the result in the currently selected cell.

    cheers, Paul

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    For the life of me, I couldn't come up with a formula either so ended up using a pivot table and referenced the results as needed. Phew.
    Thanks for viewing this.

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Try this

    Here is a whopper of an array formula that does what you want
    {=SUM(IF(FREQUENCY(
    MATCH((TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A $14&D2)),($A$2:A$14&B$2:B$14),0))),
    (TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A$14&D2 )),($A$2:A$14&B$2:B$14),0))),0),
    MATCH((TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A $14&D2)),($A$2:A$14&B$2:B$14),0))),
    (TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A$14&D2 )),($A$2:A$14&B$2:B$14),0))),0))
    >0,1))-1}
    I've broken it up into multiple lines for easier reading
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you can stand a intermediate column (may be hidden), in C2 enter:

    =1/SUMPRODUCT(($B$2:$B$14=B2)*($A$2:$A$14=A2))

    Copy from C2 and paste to C3:C14

    Then in E2:
    =SUMIF($B$2:$B$14,D2,$C$2:$C$14)

    Copy from E2 to E3:E6

    Steve

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Steve
    I was hoping you'd look at my honkin' formula and tell me - oh, this is how you can reduce it to two lines. I'm always slightly uneasy when I produce such a gigantic formula, as it often means I've chosen an unnecessarily complicated solution.

    Although in this case ...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =COUNT(1/FREQUENCY(IF($B$2:$B$14=D2,IF($A$2:$A$14<>"",$A$2: $A$14)),IF($B$2:$B$14=D2,IF($A$2:$A$14<>"",$A$2:$A $14))))
    array-entered, should do it.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I knew it!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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