+ Reply to Thread
Results 1 to 7 of 7

Understanding How Counting Uniques Works

  1. #1
    Registered User
    Join Date
    08-02-2007
    MS-Off Ver
    2007
    Posts
    25

    Angry Understanding How Counting Uniques Works

    Hi there -

    Can someone please explain how the following formula works? Specifically the &"" part:

    SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20&""))

    Thanks! It has been driving me nuts!!

    -Victoria

  2. #2
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Understanding How Counting Uniques Works

    Good afternoon nothingisthis
    Quote Originally Posted by nothingisthis View Post
    Can someone please explain how the following formula works?
    SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20&""))
    This is one of those instances where a formula works as a byproduct of how a function works in the background. The SUMPRODUCT bit can also be replaced by SUM and done as an array formula.

    When a COUNTIF function has the criteria the same as the range, then each item in the range is used each time, in turn. The "1/" bit simply divides the result of the items by 1, so 5 would become 1/5 (ie a fifth), and each of these is added together.

    Now consider this example :
    A range contains the following numbers (only two uniques) : 5, 7, 7, 7, 5, 5, 7
    There are 3 fives and four sevens. The resulting sum would add together 3 x 1/3 (for the fives - which = 1) and 4 x 1/4 (for the sevens = 1) and add the two together (=2 uniques).

    The &"" bit isn't usually needed - if you have a range containing blanks then it stops errors occuring.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    08-02-2007
    MS-Off Ver
    2007
    Posts
    25

    Re: Understanding How Counting Uniques Works

    Hi Dominic -

    That is mostly clear, but why then does a COUNTIF(C9:19,C9:19) result in 2, in the attached?
    Attached Files Attached Files

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Understanding How Counting Uniques Works

    it is really only doing
    COUNTIF(C9:19,C9) it ignores the rest of the cells c10:c19
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    08-02-2007
    MS-Off Ver
    2007
    Posts
    25

    Re: Understanding How Counting Uniques Works

    "When a COUNTIF function has the criteria the same as the range, then each item in the range is used each time, in turn. The "1/" bit simply divides the result of the items by 1, so 5 would become 1/5 (ie a fifth), and each of these is added together."

    ^This part isn't clear either. I understand that 1/ divides the results by 1, but then with your example, I'm imagining: 1/5 + 1/7 + 1/7 + 1/7 + 1/5 + 1/5 + 1/7. This doesn't equal to 2.

    I think I'm not understanding how the COUNTIF really works here...

  6. #6
    Registered User
    Join Date
    08-02-2007
    MS-Off Ver
    2007
    Posts
    25

    Re: Understanding How Counting Uniques Works

    Martin, if that's the case, then we're left with a 2.

    If we apply the formula theres: 1/2, then SUMPRODUCT(1/2).

    I think I'm not understanding a step.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Understanding How Counting Uniques Works

    It's returning the first element of an array formula.

    With the formula entered in E9 as it already is, select E9:E19. Then put the cursor in the formula bar, press and hold the Ctrl and Shift keys, and press Enter. You'll see the rest of the results.

    To finish mimicing the single formula, in another cell enter =SUMPRODUCT(1/E9:E19)
    Last edited by shg; 04-02-2010 at 02:00 PM.
    Entia non sunt multiplicanda sine necessitate

+ 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