Services offered worldwide
or
Your partner in computer software training & application development
for over 25 years
Microsoft Excel Retrieve a
Distinct Count in Pivot Tables
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:
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:
We usually create our pivot table by clicking in our table > Insert > Pivot Table > New Worksheet or Existing Worksheet > OK, as shown below:
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.
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:
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.
The pivot table now displays the correct number of 3 for customers we sold to on January 14.
Please feel free to contact us if you require Excel pivot tables, Power Query, or Power Pivot training!
© 2022 DJH Training & Application Solutions Inc.