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

Count Unique Values in a Pivot Table

This is a discussion on Count Unique Values in a Pivot Table within the Excel Questions forums, part of the Question Forums category; Hello there I have a spreadsheet with several records for each person's name. I want to have pivot tables based ...

  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
    64,179

    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
    130

    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.

Page 1 of 3 123 LastLast

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