+ Reply to Thread
Results 1 to 2 of 2

Pivot Tables: Distinct Count of records containing more than one value for a field

  1. #1
    Registered User
    Join Date
    08-26-2014
    Location
    Oregon
    MS-Off Ver
    Windows 7
    Posts
    1

    Pivot Tables: Distinct Count of records containing more than one value for a field

    Hello,

    I am working with a table of college enrollment data for students, using Excel 2013. There are many fields in the source data - there is a separate record for each student, for each term they enrolled, for each college they enrolled at. Each row contains a unique ID for the student, the dates of the term, the college name, and whether the college is a 2 or 4 year (plus a number of other fields). For example:

    Student123 Fall 2015 West College 2-year
    Student456 Fall 2015 South College 4-year
    Student789 Fall 2015 West College 2-year
    Student789 Fall 2015 South College 4-year

    I want to find out the number of unique students who attended either a 2-year, a 4-year, or both a 2 and 4-year college in Fall 2015. According to the data above, there are two unique students who attended a 2-year school, two unique students who attended a 4-year school, and one unique student who attended both a 2 and 4-year school.

    I can calculate the unique number of students attending a 2-year school, or a 4-year school using PivotTables and a calculated field based on the unique student ID, or using PowerPivot and Pivot tables and a "distinct Count" of the unique ID. But I cannot figure out a way to show how many unique students attended both a 2 and 4 year school. I can cut and paste the pivot table data back into excel and create a formula to see if there is a value in both the 2-year and 4-year column, but I believe there is a way to do that in the pivot table itself.

    Any ideas or resources? Thank you very much!
    -Natalie

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

    Re: Pivot Tables: Distinct Count of records containing more than one value for a field

    You can sometimes get a bit extra out of a pivot table by using helper columns on the data.

    In this case I used 5 extra columns. I guess the logic count be "compressed" and I could probably get away with two.

    In the first two columns (E:F) I use COUNTIFS to count the number of times a student appears in the 2-year and 4-year category.

    In column G, I determine if a student is in both or only one category. If only one category, this number is zero. If not, it's as sum of the 2-year and 4-year. I could have just as well assigned 0 and 1 or False and True.

    In column H, I determine a category based on what is in columns E:G.
    Please Login or Register  to view this content.
    It is important that the strings "2-year" and "4-year" appear in the Category for both.

    Column I contains a True / False value based on matching the Category String with the selection in Cell L1.
    Please Login or Register  to view this content.
    Cell L1 is actually a pivot table but all it has is a filter based on the Category column. This forces you to select a correct value.

    Then there is the pivot table below it. This is where your answer appears. Unfortunately you have to refresh this table after you make the selection in cell L1. This could be done automatically with some VBA.
    Attached Files Attached Files

+ 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: 0
    Last Post: 04-27-2015, 03:32 PM
  2. Count distinct records that meet multiple conditions
    By rubthebuddha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-18-2013, 09:54 AM
  3. Can pivot tables count individula records?
    By jgomez in forum Excel General
    Replies: 0
    Last Post: 10-30-2012, 12:46 PM
  4. Pivot Tables-To Find Count of Unique(Distinct)User id within the same Journal Entry.
    By Deepthik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2010, 06:09 AM
  5. Count Distinct Records - Criteria
    By Thommo in forum Excel General
    Replies: 5
    Last Post: 08-24-2009, 05:49 PM
  6. Distinct Counts in Pivot Tables
    By tw41 in forum Excel General
    Replies: 1
    Last Post: 06-19-2009, 02:28 AM
  7. [SOLVED] count distinct in Pivot table
    By soe in forum Excel General
    Replies: 1
    Last Post: 02-22-2005, 10:06 AM

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