Distinct Count In Pivot Tables

Excel 2013 and later versions contain a distinct count function for pivot tables. This allows a count to be taken of not just the individual entries in a column, but the number of unique entries.

In this example we have a worksheet listing the statistics for baseball players in postseason play. There is one row for a player's stats in any one stage of the playoffs in any one year. So Jackie Robinson's hitting stats for the 1947 World Series are listed on row, those for the 1949 World Series on another row, and so on.

If we create a pivot table and get a count of the number of playerIDs listed for each teamID by dragging the teamID field to the Rows box and the playerID to the Values box, with the setting of Count of PlayerID, we get a total counting a player's individual postseason appearances more than once.

It counts Jackie Robinson's id for each of the six World Series that he appeared in. If we want to know the unique number of players that ever appeared in the postseason for Brooklyn we can use the distinct count function. Right click on the value setting and select 'Value Field Settings' and choose Summarize value field by 'Distinct Count'.

The pivot table will then be updated to display the unique number of entries in the playerID column for each team. 133 different men played in the World Series for Brooklyn.