top of page

Microsoft Excel Retrieve a
Distinct Count in Pivot Tables

Excel Icon.png
Back Button

Let's say you would like a count of sales made to customers on a particular date in a pivot table. In the example below which includes sales by date, Excel counts both Xerox and Cargil twice instead of once. This is repeated throughout our data set, making our results incorrect.


Our data may include the same customer more than once on a particular date:  

distinct Count 1.jpg

Our pivot table displays the number of clients to whom we made a sale on that date as 5 instead of 3, counting both Xerox and Cargil twice:  

distinct Count 2.jpg

We usually create our pivot table by clicking in our table > Insert > Pivot Table > New Worksheet or Existing Worksheet > OK, as shown below:

distinct Count 3.jpg

We can then add the Date field to the Rows area, and the Customer and Sales fields to the Values area of our pivot table:

Once this is done, we right-click anywhere in the Customer field in the pivot table > point to Summarize Values By, where will see the Distinct Count option , but it is grayed out, as it is not available when creating our pivot table in the usual manner.

distinct Count 4.jpg

Let's delete the pivot table and start again. This time, make sure to check the Add this data to the Data Model option when inserting the pivot table - this uses a Power Pivot feature:

distinct Count 5.jpg

Add the Date field to the Rows area, and the Customer and Sales fields to the Values area. Right-click anywhere in the Customer field > Summarize Values By > Distinct Count. This option is now available, as we added our data to the Data Model.

distinct Count 6.jpg

The pivot table now displays the correct number of 3 for customers we sold to on January 14.

distinct Count 7.jpg

Please feel free to contact us if you require Excel pivot tables, Power Query, or Power Pivot training!

© 2022 DJH Training & Application Solutions Inc.

bottom of page