Distinct count in Excel pivot tables - KING OF EXCEL

Sunday, December 15, 2019

Distinct count in Excel pivot tables

Ever wanted to get distinct count in Excel? You can use Pivot Table to get the answer quickly. Something like this:
distinct count in Excel - pivot table demo
Here is a simple trick to add distinct count to Excel pivot tables quickly.
Let’s say you have data like this:
sample data for distinct count
As you can see, several products are repeated on each day. When you make a pivot table from this data and add product count, Excel counts all products. But we want to see just the distinct count (ie if there is a duplicate product in a day, we want to count it just once). To get distinct count in the pivot table,
These instructions work only in Excel 2016, Office 365 and Excel 2013. 
  1. Insert a pivot table from your data
  2. In the create pivot dialog, enable “Add this data to data model” option.Adding data to model - Excel pivot tables
  3. Once you have the pivot table canvas, add the product (or any other field for which you want to calculate distinct count) to the values area.
  4. Right click on the values, go to “Value field settings”.Distinct count option in Excel Pivot Table value field settings
  5. Summarize the value by “Distinct count”. This is the last option.
  6. All done!

Distinct Count in Excel Pivot Tables – Example Workbook

If you want to practice this or want to see this with an example, here is the workbook.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Popular Posts