Cannot see Distinct Count in Pivot Table calculation options

Question

Split from this thread.

Hi!  I have Excel 2016 (16.0.8326.2107) 32 bit but I do not have the "Distinct Count" option.

How do I get this option?  Thanks!

Answer 1

Hello Jimmie, 

I have split your comment on another question into a new question. 

The Distinct Count aggregation function is only available if the Power Pivot Data Model is behind the pivot table. So, when you select the data for the pivot table, tick the box to Add this data to the Data Model, as in this screenshot:

Now when you build the pivot table, you will find the Distinct Count at the bottom of the list of options to summarize values.

This option will not show if you don't add the source data to the data model. It's easily overlooked and I hope that the Excel team will include this aggregation in all pivot tables soon.

Answer 2

Hi,

Select the dataset and go to Insert > Pivot Table.  Check the box there for Add this data to the Data Model.  Click on OK.  Now build your Pivot Table.  Right click on any number in the value area section and under Summarise by > More options, the last item should be Distinct Count.

Hope this helps.