+ Reply to Thread
Results 1 to 8 of 8

Pivot Table Total for audits

  1. 05-27-2020, 02:46 PM #1
    jolthof
    jolthof is offline
    Registered User
    Join Date
    01-06-2012
    Location
    SF Bay Area, USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    Cool Pivot Table Total for audits

    I'm doing an audit. The goal is all YES. I need to know the % of YES. How do I make the grand total she the % of YES'S so that I don’t have to add an extra column with calculations?
    THANK YOU
    Julie
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-27-2020, 03:05 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    Re: Pivot Table Total for audits

    See picture attached.
    Attached Images Attached Images
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 05-27-2020, 03:23 PM #3
    jolthof
    jolthof is offline
    Registered User
    Join Date
    01-06-2012
    Location
    SF Bay Area, USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Pivot Table Total for audits

    your example provides the % for the grand total of the entire time period. This is great. I really need the % of YES for each month. is that possible?
    Reply With QuoteRegister To Reply
  4. 05-27-2020, 05:11 PM #4
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    Re: Pivot Table Total for audits

    Will presentation like this work for you? If so, then just add the Result a second time as shown below.

    Excel 2016 (Windows) 32 bit
    E
    F
    G
    H
    I
    J
    K
    L
    M
    2
    3
    Column Labels
    4
    YES
    NO
    		 Total Count of RESULT (YES/NO) Total Count of RESULT (YES/NO)2
    5
    		 Row Labels Count of RESULT (YES/NO) Count of RESULT (YES/NO)2 Count of RESULT (YES/NO) Count of RESULT (YES/NO)2
    6
    		 Jan
    11.11%
    1
    11.11%
    1
    22.22%
    2
    7
    		 Feb
    11.11%
    1
    0.00%
    11.11%
    1
    8
    		 Mar
    11.11%
    1
    0.00%
    11.11%
    1
    9
    		 Apr
    11.11%
    1
    11.11%
    1
    22.22%
    2
    10
    		 May
    22.22%
    2
    11.11%
    1
    33.33%
    3
    11
    		 Grand Total
    66.67%
    6
    33.33%
    3
    100.00%
    9
    12
    Sheet: Sheet1
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  5. 05-27-2020, 05:15 PM #5
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    Re: Pivot Table Total for audits

    Or are you looking for this. Play with the percentages until you find the one you want.

    Excel 2016 (Windows) 32 bit
    E
    F
    G
    H
    I
    J
    K
    3
    Column Labels
    4
    YES
    NO
    		 Total Count of RESULT (YES/NO) Total Count of RESULT (YES/NO)2
    5
    		 Row Labels Count of RESULT (YES/NO) Count of RESULT (YES/NO)2 Count of RESULT (YES/NO) Count of RESULT (YES/NO)2
    6
    		 Jan
    50.00%
    1
    50.00%
    1
    100.00%
    2
    7
    		 Feb
    100.00%
    1
    0.00%
    100.00%
    1
    8
    		 Mar
    100.00%
    1
    0.00%
    100.00%
    1
    9
    		 Apr
    50.00%
    1
    50.00%
    1
    100.00%
    2
    10
    		 May
    66.67%
    2
    33.33%
    1
    100.00%
    3
    11
    		 Grand Total
    66.67%
    6
    33.33%
    3
    100.00%
    9
    Sheet: Sheet1
    Reply With QuoteRegister To Reply
  6. 05-27-2020, 07:59 PM #6
    jolthof
    jolthof is offline
    Registered User
    Join Date
    01-06-2012
    Location
    SF Bay Area, USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Pivot Table Total for audits

    thank you for your help. I uploaded an example to help illustrate what i need.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 05-27-2020, 11:21 PM #7
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    Re: Pivot Table Total for audits

    My last post was the best example of getting what you are looking for. Don't know of any way of getting exactly what you want. If you are willing to upgrade your version of Excel, you might be able to achieve with Power Pivot. I am not an expert on Power Pivot but I know it has more capabilities than normal Pivot Tables.
    Reply With QuoteRegister To Reply
  8. 05-28-2020, 11:11 AM #8
    jolthof
    jolthof is offline
    Registered User
    Join Date
    01-06-2012
    Location
    SF Bay Area, USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    Cool Re: Pivot Table Total for audits

    Thank you for your help!! Ill use your last recommendation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Comparing a percentage of a total to the total in pivot table and chart
    By BillDoyle in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-27-2018, 03:56 PM
  2. total and total by selected quarter pivot table and then chart representation
    By Cdubisms in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-24-2016, 12:40 PM
  3. [SOLVED] Pivot Table Sub-Total and Total Settings for Percentages
    By Adam Schaefer in forum Excel General
    Replies: 3
    Last Post: 04-21-2016, 09:50 AM
  4. [SOLVED] Can a Pivot Table display a second Grand Total row (one for total # and another for %)?
    By sherylt13 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 09-29-2015, 08:44 PM
  5. Pivot Table Total doesn't match data total
    By CNL.Excel in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-31-2013, 07:40 PM
  6. Pivot Table - Sub Total & Grand Total Filter
    By ramki in forum Excel General
    Replies: 1
    Last Post: 08-07-2009, 11:40 PM
  7. [SOLVED] % of Running Total to Grand Total in Pivot Table
    By David in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2005, 04: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1