Search This Blog

Sunday 17 December 2017

COUNT DISTINCT VALUES USING PIVOT TABLE


I have a column "Product Category" in my dataset having 23 entries including duplicates.


I want to know the distinct number of categories. There are different ways of doing it. In this post, I am going to do it using pivot table.

Step-1: Select your data > Go to Insert Tab > Click on PivotTable or You can use keyboard shortcut Alt + NV. A dialogue box will appear to create pivot table.

Step-2: After selecting the range and worksheet where you want to place the pivot table, check "Add this data to the Data Model" (as shown below) and click OK.



This process takes a few seconds as it loads the data into data model.

Step-3: Drag your field onto the Values area in the Pivot Table list. It will display the count of Product Category in the table (overall count including duplicates).




Now, we will perform two more steps to get the distinct count.

Step-4: Go to Value Field Settings by right clicking on the field in the pivot table.


Step-5: In the appeared dialogue box, select Distinct Count as type of calculation and click OK. You will get the distinct count of product categories.


Note: "Distinct Count" will not appear as type of calculation if you don't check "Add this data to the Data Model" while creating the pivot table.

By formula : Count unique entries from a list of duplicates

Is this post helpful?

Put your valuable comments in the comment box:)

No comments:

Post a Comment