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

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.

=IF([@Both]>0,"2-year&4-year",IF([@[2-year]]>0,"2-year",IF([@[4-year]]>0,"4-year","Unknown")))

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.

=ISNUMBER(FIND($L$1,[@Category]))

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.