Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table (2000)

    I am trying to get a count of client numbers in a pivot table. The client number can be repeated more than once. Is there a way to count unique client numbers in a pivot table?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Pivot table (2000)

    In general, you can find the number of unique items in a range using the formula

    =SUM(1/COUNTIF(range,range))

    entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of Enter. Can you use that?

  3. #3
    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

    Re: Pivot table (2000)

    You can add a formula after the fact to get the number of items in that column...

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table (2000)

    I am trying to use this formula but I don't think I am entering it right. =sum(1/countif(range,range)) and then ctrl shift enter. How is the range, range part supposed to be entered. My range is A2:A11882.

  5. #5
    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

    Re: Pivot table (2000)

    =SUM(1/COUNTIF(A2:A11882,A2:A11882))

    But be aware, this 1 formula has many many calculations inherent in it and could make your sheet very "sluggish" (maybe even downright slow)

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Pivot table (2000)

    It would be
    <code>
    =SUM(1/COUNTIF(A2:A11882,A2:A11882))
    </code>
    It won't work if you have empty cells and/or zero values in the range A2:A11882. If so, change the formula to
    <code>
    =SUM(IF(LEN(A2:A11882),1/COUNTIF(A2:A11882,A2:A11882)))
    </code>
    again entered as an array formula. (Thanks to <!profile=Aladin Akyurek>Aladin Akyurek<!/profile> for this variation.)

Posting Permissions

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