+ Reply to Thread
Results 1 to 11 of 11

Creating rows to sum categories from a pivot table

  1. #1
    Registered User
    Join Date
    09-17-2018
    Location
    California
    MS-Off Ver
    2016 32-bit
    Posts
    6

    Creating rows to sum categories from a pivot table

    I'm very new to pivot tables. I've got an Excel file that I put a pivot table in which creates a report form for trash found during site surveys. Users can select the site and date of the survey, and the report shows the category, details, comments and the total number of items found for each detail.
    I'm trying to find a way to create a sum for each category. Ideally, it would be in a row at the end of the category. I don't think it's a field I can create in the source table, because it depends on the variables selected. I've tried using the Subtotal feature, but that's not giving me the desired SUM result, just adding lots of extra rows. I can;t seem to figure out how calculated fields work in this table.
    I'm attaching a screenshot. Ideally, I'd like the row inserted at the red line, summing up the highlighted numbers above.
    pivottable1.JPG

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Creating rows to sum categories from a pivot table

    It is always a good idea to attach a sample workbook, but it is particularly important when dealing with pivot tables. A lot depends on how the source data is laid out.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    09-17-2018
    Location
    California
    MS-Off Ver
    2016 32-bit
    Posts
    6

    Re: Creating rows to sum categories from a pivot table

    I'm attaching a copy of the file here.
    The "Previous Layout" sheet shows what we used to use to manually enter the information.
    "Data Collection Form" is the sheet with the pivot table that I created.
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Creating rows to sum categories from a pivot table

    I am using an old Jedi Mind Trick with a helper column.

    First of all kudos for using Excel Tables. They make life a lot easier. Also, the data are in normalized format, so you can get the maximum out of your pivot tables.

    The Column Header is called Count and it has the formula: =COUNTIFS([Monitoring Site],[@[Monitoring Site]],[Floatables Category],[@[Floatables Category]],[Floatables Category Detail],[@[Floatables Category Detail]],[Comments],[@Comments])

    I am assuming you want to break down the items to the comments level. The formula gets the count by site, category, detail and comment and displays it next to each occurrence of the combination.

    When it comes to the pivot table, if we were to SUM the item as the default indicates, then we get duplicate counts. Suppose a combination exists 3 times, then the sum will see each item and add it up 3 + 3 + 3 = 9. So instead, use Average. Average works because the same number is next to the combination in each location where it exists.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-17-2018
    Location
    California
    MS-Off Ver
    2016 32-bit
    Posts
    6

    Re: Creating rows to sum categories from a pivot table

    Thanks for the quick help. I forgot to mention that there is a column on the source sheet "Floatables Category Final Count" which I relabeled as Total on the pivot table. It has the total number of, say, plastic water bottles found. I am trying to figure out a way to then create a sum for each category. (The column name is a little confusing, as it is the final count for a Floatables Category Detail, not the overall Category) Ideally, this data would be a row underneath each category.

    For example, in this highlighted picture, I'd want to get a total of 11.pivottable2.JPG

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Creating rows to sum categories from a pivot table

    It seems I made the pivot table more complicated than it had to be. You can drop the helper column. Just get a count of comments and you're good. Right Click on the Flotables Category and select the option for a total.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-17-2018
    Location
    California
    MS-Off Ver
    2016 32-bit
    Posts
    6

    Re: Creating rows to sum categories from a pivot table

    That's awesome with the row underneath each category, but the only thing I need to change is that it's counting the number of comments instead of getting the number from that final count category. So, for example, there's no comment for "Cigarette Butts" so it comes in as 0, when there may be 20+ butts counted.

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Creating rows to sum categories from a pivot table

    Would that be Floatables Category Final Count? Many of these values are factional.

  9. #9
    Registered User
    Join Date
    09-17-2018
    Location
    California
    MS-Off Ver
    2016 32-bit
    Posts
    6

    Re: Creating rows to sum categories from a pivot table

    Yes, that's the category. I know there are some non-whole numbers in there. We're trying to figure out why some were entered like that instead of a whole number. (How does one have .1 of a cigarette butt?)

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Creating rows to sum categories from a pivot table

    Some butts are smoked down more than others? OK. I think it finally went in to the cranium.

    Try this version. I have the pivot table showing whole numbers so they are "rounded" (at least they appear to be even though they still have the original values under the hood.)
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-17-2018
    Location
    California
    MS-Off Ver
    2016 32-bit
    Posts
    6

    Re: Creating rows to sum categories from a pivot table

    YYYESSSSS!!! I can't thank you enough for your help. This was my very first attempt at a pivot table, and I was really getting stuck. I work in a smaller office and don't have any Excel people in my physical location, so I've been trying to track down someone in another office to help. I think this is going to meet what the clients want, and I'm going to get the data cleaned up and try to figure out how you did this.

+ 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] Extract 9 largest categories - similar to pivot table but need formula
    By Thanks4helping in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-19-2016, 04:32 PM
  2. Summarize many categories on 1 pivot table
    By Ron Purpura in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-01-2016, 03:21 AM
  3. Creating a Chart from select columns/rows in a Pivot Table
    By dreamer001 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-23-2015, 03:04 PM
  4. [SOLVED] Pivot Table/Chart to filter categories
    By Maxthelion in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 05-20-2014, 10:26 AM
  5. [NOT POSSIBLE] Pivot table with calculated item to show only categories > 0
    By bagullo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2011, 03:20 AM
  6. Replies: 4
    Last Post: 06-19-2009, 09:02 AM
  7. Pivot table - show absent categories
    By andrefrancis1 in forum Excel General
    Replies: 2
    Last Post: 11-07-2007, 06:00 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