+ Reply to Thread
Results 1 to 7 of 7

Count distinct value and SUM in the same Pivot table

  1. #1
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Count distinct value and SUM in the same Pivot table

    Hi All,

    Is there any method or workaround to count distinct value & SUM in the same pivot table?.

    If we tick on add data model in pivot table, we may not be able to calculate SUM

    Untitled.jpg
    1. Thank those who have helped you by clicking the Star * below the post.
    2. Please mark your post [SOLVED] if it has been answered satisfactorily.

    Sincerely,
    Farid

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Count distinct value and SUM in the same Pivot table

    Why would you want to sum a text field??
    Rory

  3. #3
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Count distinct value and SUM in the same Pivot table

    Hi Rory,

    I need to count unique value of borrower group & borrower name, sum of outstanding value, total approved limit and so forth. I know excel formula can solve it but it would be nice if both requirement can be done in the same pivot table.

    I have many more columns and thus it would be easy to use pivot table since we are able to drag in/out during presentation.

    Ok, if I do not add data model to pivot table, Is there any workaround to count unique value? Perferably without using formula because time consuming during presentation.
    Last edited by Faridwahidi; 03-11-2019 at 08:39 PM.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Count distinct value and SUM in the same Pivot table

    My point is that you can add the data to the model and then sum and count distinct. You just can't sum a text field.

  5. #5
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Count distinct value and SUM in the same Pivot table

    Hi rory,

    When data added to the model, SUM will not working. I have tried many times. If you can do it, I would much appreciate if you can attach sample file.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Count distinct value and SUM in the same Pivot table

    The data model would be entirely pointless if you couldn't sum anything.

    Please post your sample workbook and explain what you are trying to sum that you can't.

  7. #7
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Count distinct value and SUM in the same Pivot table

    Hi rory,

    Sorry, I was overlooked both columns to count sum are text format. After change to number format, it worked.

    Anyway, thanks for responding.

+ 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. VBA Pivot Table Distinct Count
    By matth3881 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-02-2022, 01:02 AM
  2. Help with distinct count on Pivot table
    By Jonathan11235 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-27-2016, 07:25 AM
  3. No distinct count function in pivot table
    By AS Mcclain in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 09-25-2015, 12:37 PM
  4. 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
  5. 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
  6. [SOLVED] count distinct in Pivot table
    By soe in forum Excel General
    Replies: 1
    Last Post: 02-22-2005, 10:06 AM
  7. [SOLVED] 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