Using Pivot to get a count of distinct values

Nick Sor

Hello,

Is it possible to use Pivot to get a count of distinct values? I want to count the number of unique values in a column, but am only able to see options for SUM, AVERAGE, and, COUNT.

Thank you

Paul Newcome

You would need to use a formula.

=COUNT(DISTINCT([Column Name]:[Column Name]))

Nick Sor

Hi Paul,

Thank you for the response. I am trying to get a distinct count in one column, grouped by the values in another column, and separated out by a different column. I am trying to get a distinct count of Building IDs, based on if they are owned or leased and group those counts by Sub Region.

Currently I am getting this count by using a separate sheet to get these calculations, but as our list of sub regions grow I am trying to look for a solution that is more flexible to avoid having to manually add a new column/formula for each new sub region.

The below image is what I currently have set up in Pivot pulling information from the source sheet. I am able to get it to produce a sheet separated by lease type and grouped by sub region, but it is giving me an overall count rather than a distinct count.