+ Reply to Thread
Results 1 to 2 of 2

Advance Pivot Table

  1. #1
    Registered User
    Join Date
    04-29-2019
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    2

    Advance Pivot Table

    Hi,
    Can i customize pivot table row field with some formula instead?
    2.jpg
    i made an example that you can see in the picture above. The goal is to made a summary of how much the sales person(SP) made using pivots (SP Performance). I need to use pivots so i can make slicer tool of selected salesperson.
    If someprojects is handled by more than 1 SP, the amount should be fully added on both SP Performance

    If anyone has suggestion to handle the raw data, or how to customize the pivots please help me.
    Feel free to ask by the way, Thanks before

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Advance Pivot Table

    Perhaps the following will help.
    The sample data is in columns A:E
    Column F is populated using: =IF(A2=A1,F1&"/"&D2,D2)
    Column H is populated using: =IFERROR(IF(MATCH(0,INDEX(COUNTIF(H$1:H1,A$2:A$20),,),)>COUNTA(A$2:A$20),"",INDEX(A$2:A$20,MATCH(0,INDEX(COUNTIF(H$1:H1,A$2:A$20),,),))),"")
    Columns I:J are populated using: =IFNA(INDEX(B$2:B$20,MATCH($H2,$A$2:$A$20,0)),"")
    Column K is populated using: =INDEX(F$2:F$20,AGGREGATE(14,6,(ROW(A$2:A$20)-ROW(A$1))/(A$2:A$20=H2),1))
    Note that columns F:K may be moved and/or hidden for aesthetic purposes.
    In the display table columns P:S are populated using: =IFERROR(INDEX(H$2:H$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$1))/(ISNUMBER(SEARCH($O$1&"*",$K$2:$K$6))),ROWS($A$1:$A1))),"")
    Column T is populated using: =SUMIFS(E$2:E$20,A$2:A$20,P2)
    Test by changing the value in O1 from B to A.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Excel formula in finding the data in the table (Thank you in advance!)
    By SMIBCI in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2017, 04:34 AM
  2. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  3. Replies: 1
    Last Post: 07-29-2015, 05:15 PM
  4. Advance/Complex Table pivot or filtering need
    By arctushar in forum Excel General
    Replies: 3
    Last Post: 08-30-2014, 08:44 PM
  5. Copy a table to multiple sheets and advance the formulae
    By benjaminjrp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2013, 06:15 PM
  6. advance pivot
    By techytechy in forum Excel General
    Replies: 0
    Last Post: 11-07-2011, 01:06 PM
  7. Advance filtering or consolidated pivot????
    By martyp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2010, 01:36 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