+ Reply to Thread
Results 1 to 9 of 9

Pivot: Summarize Services for Client and Net Profits Calculation

  1. #1
    Registered User
    Join Date
    01-13-2021
    Location
    Switzerland
    MS-Off Ver
    Excel 2019
    Posts
    4

    Pivot: Summarize Services for Client and Net Profits Calculation

    EDIT:

    Hi Belinda

    Many thanks for getting back to me.

    Alright, I have made some example of the end document how it shall look like.
    Please find attached an excel with 3 sheets. One with the timesheet data, one with the fees that shall be billed and one with the Pivot table.

    What is in column D of the Pivot table is what should effectively be in column C(if I made the settings in the pivot correctly).
    If you could help with instructing about the correct setting directly with the Pivot it would of course be most helpful.

    In addition I would like to make another column with the subtraction of column b - column c in order to get the net profit (within the Pivot table).

    Is this something you might be able to support me with?

    Speaking of - is there anyone who could move the post to the Pivot section?
    Attached Files Attached Files
    Last edited by Sandro1992; 01-13-2021 at 05:13 PM. Reason: To specify

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: Summarize Services for a Client

    why screen shot and not the file itself?


    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    01-13-2021
    Location
    Switzerland
    MS-Off Ver
    Excel 2019
    Posts
    4

    Re: Summarize Services for a Client

    --> base post edited for overview reasons
    Last edited by Sandro1992; 01-13-2021 at 05:09 PM.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: Pivot: Summarize Services for Client and Net Profits Calculation

    Hi
    Please see "Amended PVT" tab in the attached file.
    I added another column in your timesheet with the below formula:
    L2 and down:
    =IF(SUMPRODUCT(--($J$2:J2&$K$2:K2=[@Client]&[@Services]))=1,VLOOKUP([@Services],'Services Overview'!A:B,2,0),"")
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: Pivot: Summarize Services for Client and Net Profits Calculation

    As for the calculated field - go to Pivotable tools---fields, items&sets----calculated field----
    Put the name of the title you would like to have
    in the formula section: = 'Original WIP Value EC'- Fee
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-13-2021
    Location
    Switzerland
    MS-Off Ver
    Excel 2019
    Posts
    4

    Re: Pivot: Summarize Services for Client and Net Profits Calculation

    Awesome, that is exactly what I was searching for!

    One small thing what I do not understand how to look up - (at)Clients & (at)Services. Is there any chance to see what is behind those placeholders, respectively how can I move them to a different column e.g.?
    I see that - if I delete the name after the (at) I can select any column. However, When I want to do the same on a different sheet I noticed that I first need to add some sort of function which I am not aware of
    Last edited by Sandro1992; 01-13-2021 at 05:43 PM.

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: Pivot: Summarize Services for Client and Net Profits Calculation

    This is because you have a table formatting (which I presonally cannot stand )

    if you convert the table to a range you can see the cell refernce instead of the header titles (table tools----convert to range)
    If you would like to maintain the table formatting - you can change the titles and type manually the cell numbers (J2&K2 for example in L2, and J3&K3 etc, as you drag it down).

    Hope this helps...

  8. #8
    Registered User
    Join Date
    01-13-2021
    Location
    Switzerland
    MS-Off Ver
    Excel 2019
    Posts
    4

    Re: Pivot: Summarize Services for Client and Net Profits Calculation

    Wow - so easy =)
    Where can I give tips? You've really helped me out, thanks a lot!

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: Pivot: Summarize Services for Client and Net Profits Calculation

    you're welcome
    You can press add reputation under my username, and thank you.

+ 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: 01-24-2018, 06:09 PM
  2. [SOLVED] Want to retrieve/display client visit dates by client name
    By jb5150 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-07-2017, 04:26 PM
  3. [SOLVED] Client Data in Columns - how to create a client drop down
    By abearmenta in forum Excel General
    Replies: 9
    Last Post: 10-10-2016, 10:24 AM
  4. Replies: 1
    Last Post: 03-25-2016, 02:19 AM
  5. [SOLVED] Moving variable data from multiple same-client records to one client record
    By jkilday4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2015, 02:32 PM
  6. One spreadsheet but adding hours on a client by client basis..
    By arthurArthur in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-01-2013, 01:58 AM
  7. format cell to recall client name-enter a client #
    By cheryl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2005, 06:05 PM

Tags for this Thread

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