Is there a Count Distinct in Pivot Tables?

[Berrett]

I have a workbook and a pivot table.

On the pivot table I have
ROWS: Ethnicity and Behavior Response

Behavior response includes items like Restraint and Suclusion

On VALUES I have a count of Student ID.

So, naturally it is counting the number of student IDs in each Ethnicity in each Behavior Response. So I have a row that says for American Indian / Alaskan Native students there are 13 incidences of Restraint.

However, when you click into the data you find that all 13 incidents are the same student. What I would like to do is count the DISTINCT Student IDs in each Ethnicity and Behavior Response category -- so it should return 1 instead of 13.

Can this be done?

 

[Jerry Sullivan]

Hi Berrett,

Standard PivotTables in Excel do not have Distinct Count as an option in the aggregate types. If you have PowerPivot installed, you can use Distinct Count on a PivotTable created with PowerPivot.

For earlier versions of Excel that don't support PowerPivot, there are some workarounds that involve adding a helper column to your dataset, or using an SQL Query.