Data Analytics

How to Count Distinct in a pivot table?

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.

Missing Count Distinct

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

Excel Formula Count Distinct

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.

Comparison Formula vs Data Model

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.

Follow me on Linkedin, Medium, GitHub for more stuff like this

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.