+ Reply to Thread
Results 1 to 7 of 7

Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple column

  1. #1
    Registered User
    Join Date
    11-21-2014
    Location
    DC, USA
    MS-Off Ver
    2007
    Posts
    4

    Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple column

    I'm not sure why it's so hard to find an answer to simple things but it is. Not one example exists. Apparently, no one thinks like me.
    I have a bunch of columns I created: Date Columns and Text Columns. The Text Columns contain either TRUE or FALSE. Three column headings: R1, R2, and R3 with each column containing TRUE or FALSE. I simply want a total of TRUE values for each column heading in a pivot table. You pull 1 column into Row Label and Values, no problem. You pull two columns in, EXPLOSION. Values no longer correct. Not sure why but that's what happens. Trying to figure out how many TRUEs there are for each Column heading. Simple right? Wrong. See attachment. "Master" is my source and "Latest" is my pivot.
    It's kind of crazy but all examples I've come across absolutely no one has a data set up like mine where they show how to do this.
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple co

    Hello,

    your data is already in a report format and cannot be used to create the pivot report you describe (as far as I understand). Put the data into a table with four columns:

    StartDate | EndDate | Rcategory | Rvalue

    The values in Rcategory are either R1, R2 or R3. The Rvalues are either TRUE or FALSE.

    Now you can create a pivot table where you drag the Rcategory into the row area. Drag Rvalues into the value area and again into the page filter. Set the filter to TRUE.

    pivot.png

    See attached file.

    cheers, teylyn
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-21-2014
    Location
    DC, USA
    MS-Off Ver
    2007
    Posts
    4

    Re: Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple co

    Thanks for the reply. The data will be coming in automatically as I have laid out in the "Master" sheet. I'm trying to create a pivot table based on that. Creating one isn't the issue. When I try to pull in more than one R column (R1, R2, R3), the data seems to get confused so that I can't get a total for each column value (based on TRUE value). Just trying to get a count of "TRUE" values for each column.

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple co

    That's how pivot tables work. If you pull several columns into the row area, they form a hierarchy and you will not be able to filter on individual columns.

    What you describe can be done by a simple table, where the columns exist side by side, not in a hierarchy.

  5. #5
    Registered User
    Join Date
    11-21-2014
    Location
    DC, USA
    MS-Off Ver
    2007
    Posts
    4

    Re: Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple co

    So you're saying not to use a pivot table but to use a regular table. And from that, I should be able to pull in all columns (R1, etc) and get a count of the TRUE and FALSE for each column?

  6. #6
    Registered User
    Join Date
    08-19-2015
    Location
    California, USA
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple co

    Thanks for the insight on what you can and can't do with pivot tables. This thread was a life saver.

  7. #7
    Registered User
    Join Date
    08-19-2015
    Location
    California, USA
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple co

    Aside from writing a macro which I'm probably about to do how would you convert a table with the first format talked about to teylyn's solution? Which would basically mean make an entry in a new table for each cell in R1 -R3

+ 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. Combining value count from multiple columns in pivot table
    By AFsimRA in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-05-2013, 11:29 AM
  2. Replies: 0
    Last Post: 10-16-2012, 05:17 AM
  3. Replies: 5
    Last Post: 04-21-2011, 05:22 PM
  4. Pivot Table - Count data From Multiple Columns
    By Ashraf1 in forum Excel General
    Replies: 21
    Last Post: 02-23-2011, 08:13 AM
  5. Replies: 0
    Last Post: 01-29-2010, 06:40 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