Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Count Unique Values in a Pivot Table

  1. #1
    Board Regular audiate's Avatar
    Join Date
    Jun 2003
    Posts
    79

    Default Count Unique Values in a Pivot Table

    Hello there

    I have a spreadsheet with several records for each person's name.
    I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

    When I set up a basic Pivot, it counts each occurence of the person's name.

    Is there some way to have it count unique values only?

    Thanks

  2. #2
    Board Regular
    Join Date
    Feb 2004
    Location
    Andover, MA
    Posts
    188

    Default Re: Count Unique Values in a Pivot Table

    If you put the field with the names into the data section, you should be able to get the number of names as a count.
    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
    --Albert Einstein

  3. #3
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default

    Try adding the persons name twice once as a row heading and once as a data field (count). Here's my example but my pivot table didn't come out formatted by I'm using HTLMMakerLite.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl97 : OS = Windows NT 4
    File Edit View Insert Options Tools Data Window Help About
    =

    A
    B
    C
    D
    E
    F
    G
    1
    SalesPersonRev SalesPersonDataTotal
    2
    sam100 bobCount of SalesPerson3
    3
    sam101 Sum of Rev320
    4
    pam102 pamCount of SalesPerson5
    5
    pam103 Sum of Rev526
    6
    pam104 samCount of SalesPerson6
    7
    bob105 Sum of Rev645
    8
    bob106 Total Count of SalesPerson 14
    9
    pam107 Total Sum of Rev 1491
    10
    sam108
    11
    bob109
    12
    pam110
    13
    sam111
    14
    sam112
    15
    sam113
    16
    17
    cashflow

    [HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Hope this helps!
    Loren

    The numbers don't lie, but sometimes they don't tell the whole truth.

  4. #4
    Board Regular audiate's Avatar
    Join Date
    Jun 2003
    Posts
    79

    Default Re: Count Unique Values in a Pivot Table

    Thanks for your reply, but I think you didn't quite understand the question.

    I want each PERSON only counted ONCE. I dont' want 2 of Bob and 5 of Pam;
    basically I need to do pivots by different categories, but just show how many people (1 count for 1 person) fall under each category.

  5. #5
    Board Regular
    Join Date
    Feb 2004
    Location
    Andover, MA
    Posts
    188

    Default Re: Count Unique Values in a Pivot Table

    When you put the name field in the data section, double click on it, choose Count, then click options and choose index. That should give you the count of the unique names, not a count of names
    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
    --Albert Einstein

  6. #6
    Board Regular audiate's Avatar
    Join Date
    Jun 2003
    Posts
    79

    Default Re: Count Unique Values in a Pivot Table

    Thank you thank you thank you!!

    (now why can't they have that in the help menu??)

  7. #7
    Board Regular audiate's Avatar
    Join Date
    Jun 2003
    Posts
    79

    Default Re: Count Unique Values in a Pivot Table

    I just found out something...

    I tried out the Index function and at first thought it had solved my problems.

    However, all the subtotals now show as a "1" as well.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Page.htm___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    DATA
    2
    NameCategoryDateAward Amount
    3
    Ben SmithA12-Jan-03 15,000
    4
    Jan FehrA02-May-02 50,000
    5
    Ben SmithA03-Sep-01 90,000
    6
    Armand MooreC13-Oct-02 23,000
    7
    8
    PIVOT
    9
    Data
    10
    CategoryNameCount of Name - IndexCount of Name - Normal
    11
    ABen Smith12
    12
    Jan Fehr11
    13
    A Total 13
    14
    CArmand Moore11
    15
    C Total 11
    16
    Grand Total 14
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    In the example, under "Count of Name - Index" each name is counted once but the subtotal for category A shows as 1 instead of 2.
    If I change it back to the normal "count" (next column) my subtotals are still wrong because it is now counting every instance of the name.

    Solutions???

  8. #8
    Board Regular
    Join Date
    Mar 2005
    Location
    Bogotá, Colombia
    Posts
    60

    Default Re: Count Unique Values in a Pivot Table

    I am having the same problem you are...did you find a work around for this? Now I have to do two pivot tables in order to count the unique person (im trying to find out number of patients per day from a DB...each row of the DB corresponds to a study, and a patient can have several studies / day).

    Edit: For the record: I did the trick as described here:

    http://www.contextures.com/xlPivot07.html

    Count Unique Items

    In a pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable.

    For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'

    In the first data row, enter a formula that refers to the customer and item columns. For example:

    =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

    Copy the formula down to all rows in the database.

    Then, add the field to the data area of the pivot table.
    Last edited by judas; Aug 14th, 2009 at 10:53 AM. Reason: Found a Workaround

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,801

    Default Re: Count Unique Values in a Pivot Table

    Quote Originally Posted by audiate View Post
    Hello there

    I have a spreadsheet with several records for each person's name.
    I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

    When I set up a basic Pivot, it counts each occurence of the person's name.

    Is there some way to have it count unique values only?

    Thanks
    Something like...

    ItemValueD-ScoreItemDataTotal
    a30.25aSum of Value17
    a40.25Sum of D-Score1
    b21bSum of Value2
    c60.5Sum of D-Score1
    d71cSum of Value10
    a80.25Sum of D-Score1
    c40.5dSum of Value7
    a20.25Sum of D-Score1
    Total Sum of Value36
    Total Sum of D-Score4


    A1:B9 houses the source data.

    C1: D-Score

    C2, just enter and copy down:

    =1/(COUNTIF($A$2:$A$9,A2))

    Then build a pivot table, where Total Sum of D-Score would constitute the unique count.

    Also:

    Control+shift+enter, not just enter...

    =SUM(IF(FREQUENCY(IF(A2:A9<>"",MATCH("~"&A2:A9,A2:A9&"",0)),ROW(A2:A9)-ROW(A2)+1),1))

    would yield that count.

  10. #10
    Board Regular WaxonWaxov's Avatar
    Join Date
    Apr 2009
    Location
    Charlotte, NC, USA
    Posts
    140

    Default Re: Count Unique Values in a Pivot Table

    Quote Originally Posted by AlohaJulio View Post
    When you put the name field in the data section, double click on it, choose Count, then click options and choose index. That should give you the count of the unique names, not a count of names
    I don't see the options thing you reference. I am using Excel 2007. Is this something else the folks at MSFT took away from us.

    I desperately need the answer to this question.
    ~I know more about Excel than I ever wanted to.

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
  •  


DMCA.com