+ Reply to Thread
Results 1 to 11 of 11

Create Pivot table

  1. #1
    Forum Contributor
    Join Date
    05-09-2015
    Location
    Thailand
    MS-Off Ver
    MS365
    Posts
    303

    Create Pivot table

    Dear Guru
    Please help for this as attached file.
    I need to count customer type as green table when show same column B need to show only 1 and to have total sub = sum of them but when I use pivot formula , it's show summary of record of customer type pls see in attached file, as i do i can get by need but i need to column J (Sum to show in row panel below each company column but i can't do. i need as green table format but need to show from pivot. how can i do this. pls suggest .
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create Pivot table

    You can't do what you are after using current source and pivot alone. Subtotal/total uses same calculation logic, but on subset or entire set.

    MIN() will still be min value over set. What you are looking to do will require one of following.

    1. Add helper column to source, using COUNTIF()

    2. Create pivot with repeat labels and in classic format. Create dynamic named range based on the pivot, then use that as data source for final pivot.

    3. If using Excel 2013 or later, use Distinct Count (by adding data to data model).

    In your case, easiest method is 1.

    Add helper:
    =IF(COUNTIFS($B$2:B2,B2,$A$2:A2,A2)>1,0,1)
    Copy down.

    Extend Pivot Range.
    0.JPG
    Last edited by CK76; 10-16-2018 at 09:21 AM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    05-09-2015
    Location
    Thailand
    MS-Off Ver
    MS365
    Posts
    303

    Re: Create Pivot table

    hi
    thanks a lot , i need some ask that when i use value value field setting , i need to put sum or count? i tried but don't show as your pic.
    pls suggest.

  4. #4
    Forum Contributor
    Join Date
    05-09-2015
    Location
    Thailand
    MS-Off Ver
    MS365
    Posts
    303

    Re: Create Pivot table


  5. #5
    Forum Contributor
    Join Date
    05-09-2015
    Location
    Thailand
    MS-Off Ver
    MS365
    Posts
    303

    Re: Create Pivot table

    oh i can do like you. tried to switch many time. but if i re do again with new file may be can same thing.
    pls suggest how to do

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create Pivot table

    See attached sample.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-09-2015
    Location
    Thailand
    MS-Off Ver
    MS365
    Posts
    303

    Re: Create Pivot table

    In case i have more than coulumne before current column then i need to put more column after countifs right? In current there are. Two condition in countifs function (....,....)
    Pls sugest

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Create Pivot table

    The problem with using an additional column is that the calculated total of unique values gives 9 and should be 8. My solution is to use a pivot table in the data model.
    Attached Files Attached Files

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create Pivot table

    @kimudao

    I'm not sure what you mean?

    @stasinek

    From OP's sample, I took it as not distinct over entire set, but within Subset within Company.
    Distinct count by using model (without use of PowrePivot/DAX) is only available from Excel 2013 or later.

  10. #10
    Forum Contributor
    Join Date
    05-09-2015
    Location
    Thailand
    MS-Off Ver
    MS365
    Posts
    303

    Re: Create Pivot table

    Hi guru
    like this one
    Attached Files Attached Files

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create Pivot table

    ... you forgot to refresh pivottable. That's why it's showing 0 for east, hhh, E.

+ 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: 6
    Last Post: 01-24-2017, 06:56 PM
  2. [SOLVED] calculating specific data from a pivot table to create a pivot chart in Excel 2016
    By cinstanl in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-15-2016, 12:14 PM
  3. How to create a dynamic macro that will create pivot table
    By thlee1122 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2015, 11:49 PM
  4. How to create a macro to create a pivot table on a new sheet
    By thlee1122 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2015, 11:14 AM
  5. Replies: 1
    Last Post: 07-22-2015, 07:16 AM
  6. Create a macro to create a pivot table in a worksheet
    By Triscia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-15-2015, 10:53 AM
  7. [SOLVED] Create Pivot Table: Cannot Open Pivot Table Source File
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-05-2010, 05:11 AM

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