+ Reply to Thread
Results 1 to 6 of 6

Pivot Table that performs a distinct count in excel 2013

  1. #1
    Registered User
    Join Date
    01-13-2010
    Location
    Cadillac, MI
    MS-Off Ver
    Excel 2003
    Posts
    81

    Pivot Table that performs a distinct count in excel 2013

    I'm using 2013, and need to use the count function in the pivot table. However I need it to be a distinct/unique count versus a count of every row. I've read somewhere else that in order to do this, I can hit the check box "add this data to the data model" during the first step of creating a pivot table, and this will enable me to create the distinct count. I see where the checkbox is, however it's grayed out and doesn't even allow for me to check it. Is there a solution to why I can't check the box? Or does someone know if there is another way to do a distinct count in a pivot table? thanks for any assistance.

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Pivot Table that performs a distinct count in excel 2013

    Is the workbook a .xls file?
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Registered User
    Join Date
    01-13-2010
    Location
    Cadillac, MI
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Pivot Table that performs a distinct count in excel 2013

    it is a xls file, so I'm assuming I need to change it to an xlsx. but even an xlsx I'm unsuccessful... the box is still grayed out
    Last edited by tdf2437; 09-09-2014 at 08:35 AM.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Pivot Table that performs a distinct count in excel 2013

    Yes. You may not have Data Model in xls file.

  5. #5
    Registered User
    Join Date
    01-13-2010
    Location
    Cadillac, MI
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Pivot Table that performs a distinct count in excel 2013

    ok, what I did was copy all the data and paste into a new worksheet and saved it as an xlsx file. I began creating the pivot table, and the box was able to be checked. So thanks for helping out with that step.
    But now I'm having troubles with the next step. I add the columns to the pivot table, and go into Value Field Setting for the specific column that I want as Distinct Count. However, there is no choice in the dropdown menu for Distinct Count. The last function to show in the drop down is Varp

  6. #6
    Registered User
    Join Date
    01-13-2010
    Location
    Cadillac, MI
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Pivot Table that performs a distinct count in excel 2013

    I think I solved the last issue.
    thanks Izandol for the assistance

  7. #7
    Registered User
    Join Date
    01-13-2010
    Location
    Cadillac, MI
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Pivot Table that performs a distinct count in excel 2013

    I think I solved the last issue.
    thanks Izandol for the assistance

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 06-19-2014, 12:59 PM
  2. How to count distinct values from table in pivot table
    By gopijadhav in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-04-2014, 07:14 AM
  3. [SOLVED] How do I set up Distinct Count in a Pivot Table?
    By STOFF in forum Excel General
    Replies: 1
    Last Post: 04-18-2006, 01:25 PM
  4. count distinct in Pivot table
    By soe in forum Excel General
    Replies: 1
    Last Post: 02-22-2005, 10:06 AM
  5. Count Distinct Values by Group Using Pivot Table (NM)
    By MCP in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2005, 06:06 PM

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