+ Reply to Thread
Results 1 to 6 of 6

Multiply two columns of data into a new column in pivot table

  1. #1
    Registered User
    Join Date
    03-25-2013
    Location
    Delaware, OH
    MS-Off Ver
    Office 2019/Office 365
    Posts
    65

    Multiply two columns of data into a new column in pivot table

    I have exported some employee data including hours worked and hourly rate. I have attached an excel spreadsheet with a pivot table constructed form the data. I would like to be able to multiply the Grand Total of hours by the Hourly Rate to get a salary total in the far right column. Additionally I suspect it will be included in the column totals giving me a grand total at the bottom of the page as well as each department total. I am not sure if this is possible but I hope so. If you have any additional questions let me know. Thank You.
    Attached Files Attached Files

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

    Re: Multiply two columns of data into a new column in pivot table

    According to an article, search for "excel using grand total in calculated field", you can not make a calculated field, that's how you would do this, using either pivot table totals or sub totals. I will offer a faux field solution however. Paste the following formula in J5 then copy down as far as needed:
    Please Login or Register  to view this content.
    As you can tell by looking at the formula this solution utilizes two helper columns, which can be hidden for aesthetics, to find the beginning of the range for the subtotal.
    Here is a copy of your file with the formula applied: Copy of Auditor Report.xlsm
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    03-25-2013
    Location
    Delaware, OH
    MS-Off Ver
    Office 2019/Office 365
    Posts
    65

    Re: Multiply two columns of data into a new column in pivot table

    Thank you so much for the information. That helps. However I have noticed that the Departments Totals are incrementing from one department to another. For example, Dept. 1 total is in the Dept. 8 total along with the employee totals and all totals are in Dept. 203 total. Other than that it is great. I am not sure how to fix this problem myself so if you don't mind I would appreciate showing me how to handle this problem. Thank You.

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

    Re: Multiply two columns of data into a new column in pivot table

    This modification to the formula in J5 and down corrects both the sub totals and grand total, for the sample file:
    Please Login or Register  to view this content.
    Let me know if you have any questions.

  5. #5
    Registered User
    Join Date
    03-25-2013
    Location
    Delaware, OH
    MS-Off Ver
    Office 2019/Office 365
    Posts
    65

    Re: Multiply two columns of data into a new column in pivot table

    Awesome, Thank you so much for your help! Have a great day!

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

    Re: Multiply two columns of data into a new column in pivot table

    You're Welcome, thank you for the feedback and for marking the thread 'Solved'. I hope that you have a great day too.

+ 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: 1
    Last Post: 07-22-2015, 07:16 AM
  2. [SOLVED] How to Multiply a Pivot Table Subfield as a Calculated Field
    By figo12 in forum Excel General
    Replies: 1
    Last Post: 11-12-2014, 06:38 PM
  3. Pivot table - value field - multiply
    By seyss in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-25-2014, 12:13 PM
  4. Replies: 6
    Last Post: 03-03-2014, 04:16 PM
  5. Values from different columns lined up in one column, in pivot table?
    By dromedar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 03:00 PM
  6. Replies: 2
    Last Post: 03-07-2013, 02:16 PM
  7. Replies: 7
    Last Post: 01-30-2005, 02: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