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