Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Count Unique with on multiple If

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Count Unique with on multiple If

    I need a unique count of users within each week.
    Sample Data:
    Date Name Week Number Sum of Unique Count of users each week (Need Formula for this one) Expected RESULT
    2/1/2017 Donald 5 3
    2/1/2017 Goofy 5 3
    2/1/2017 Minnie 5 3
    2/2/2017 Donald 5 3
    2/2/2017 Minnie 5 3
    2/15/2017 Donald 7 2
    2/16/2017 Donald 7 2
    2/16/2017 Minnie 7 2

    Any ideas on this one?

  2. #2
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    442
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique with on multiple If

    Why would you get a 3 For Donald in Week-5? Same for Goofy and Minnie?


    I would expect Donald to be a 2 for Week-2, Goofy to be a 1 and Minnie to be a 1.

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique with on multiple If

    I am trying to get a unique count of users each week. In Week number 5 Donald, Goofy, and Minnie have entries. So the count is 3. However, in Week 7 only two people have entries.

  4. #4
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,728
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count Unique with on multiple If

    Assuming there will be no blank cells in the name column try:
    This is an array formula and must be entered with CTRL-SHIFT-ENTER.
    Drag formula down as needed.

     ABCD
    1DateNameWeek NumberSum of Unique Count of users each week (Need Formula for this one)
    22/1/2017Donald53
    32/1/2017Goofy53
    42/1/2017Minnie53
    52/2/2017Donald53
    62/2/2017Minnie53
    72/15/2017Donald72
    82/16/2017Donald72
    92/16/2017Minnie72

    Spreadsheet Formulas
    CellFormula
    D2{=SUM(IF(FREQUENCY(IF($C$2:$C$9=C2,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2)+1),1))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  5. #5
    New Member
    Join Date
    May 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique with on multiple If

    I was able to get the right result by adding four new columns and using each column to do part of what I needed:
    In Column F I created a unique combination of User, Year and Week Number with this formula: =B2&"-"&YEAR(A2)&"-"&WEEKNUM(A2)
    In Column G I created a Unique Week and Year number to use in the SumIF formula: =YEAR(A2)&"-"&WEEKNUM(A2)
    In Column H I created a count formula that is fixed such that it will count the first time it shows up: =IF(COUNTIF($F$1:F2,F2)>1,"",1)
    Finally, in Colunm I I created a SumIF formula that uses Column G to and then sum the values in Column H: =SUMIF($G$2:$G$9,G2,$H$2:$H$9)

    I am still hoping to find one formula that would do all of this. But this at least gets me the right result.

  6. #6
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,728
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count Unique with on multiple If

    Did you try the formula in post#4?

  7. #7
    New Member
    Join Date
    May 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique with on multiple If

    Yes, but now my spreadsheet crashes.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,464
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Count Unique with on multiple If

    Quote Originally Posted by TJ flyer View Post
    Yes, but now my spreadsheet crashes.
    Care to post the formula as you implemented it?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    May 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique with on multiple If

    I used the same formula that is in post #4 . My actual sheet has over 50,000 rows of data and my computer freezes up when I inserted that formula and expanded it to include all of the data. For some reason my own solution of adding four columns to get to the same result does not have the same affect on the performance.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,464
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Count Unique with on multiple If

    Quote Originally Posted by TJ flyer View Post
    I used the same formula that is in post #4 . My actual sheet has over 50,000 rows of data and my computer freezes up when I inserted that formula and expanded it to include all of the data. For some reason my own solution of adding four columns to get to the same result does not have the same affect on the performance.
    Is this admissible qua performance?

    In D2 control+shift+enter, not just enter, and copy down:
    Code:
    =IF(ISNUMBER(MATCH(C2,$C$1:C1,0)),VLOOKUP($C2,$C$1:D1,2,0),SUM(IF(FREQUENCY(IF($B$2:$B$9<>"",
         IF($C$2:$C$9=$C2,MATCH($B$2:$B$9,$B$2:$B$9,0))),ROW($B$2:$B$9)-ROW($B$2)+1),1)))
    
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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