Using Pivot to get a count of distinct values



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
    Paul Newcome ✭✭✭✭✭✭

    You would need to use a formula.

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

  • Nick Sor
    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.