Hello Learners,

Today, I am going to talk about a handy and simple Excel formula which can help you count distinct values in a pivot table. When we create a pivot table and look for field values options we can see that we can summarize values by Count, Sum, average but there is no option for count distinct.

To overcome this limitation, an easy trick is to create a calculated column in the source range by using the below formula.

Copy Formula Here>>        =IF(COUNTIF($B$3:$B3,B3)=1,1,0)

<<Download Sample Workbook Here>>

There are some other ways to achieve the same solution like adding your data to data model, however it comes with it’s own disadvantages. Below is the quick comparison of both the approaches. In case you need to learn the data model approach, please refer to the sample workbook.

Although Microsoft should  seriously consider to put this for generic pivot tables (created without Data Model), the above solutions should help you out for now. If you have better ideas feel free drop a comment.