Using Pivot to get a count of distinct values
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
Answers
-
You would need to use a formula.
=COUNT(DISTINCT([Column Name]:[Column Name]))
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives