How to Count Unique Values within a Pivot Table?

%3CLINGO-SUB%20id%3D%22lingo-sub-390472%22%20slang%3D%22en-US%22%3EHow%20to%20Count%20Unique%20Values%20within%20a%20Pivot%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390472%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20a%20pivot%20table%20(attached)%2C%20I%20would%20like%20to%20add%20a%20column%20to%20show%20a%20count%20of%20unique%20names.%26nbsp%3B%20Effectively%20for%20a%20small%20batch%20with%20only%20one%20territory%2C%20it%20is%20easy%20to%20do%20a%20%3DCOUNTA%20for%20the%20names%20listed.%26nbsp%3B%20But%20how%20can%20I%20do%20this%20for%20multiple%20names%20in%20multiple%20territories%20within%20a%20pivot%20table%3F%20(~20%2C000%20lines%20to%20analyze)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPivot%20Setup%3A%3C%2FP%3E%3CP%3ETerritory%20(Country1)%20is%20in%20Rows%3C%2FP%3E%3CP%3EName%20(Aaron%2C%20Adrian%2C%20etc)%20is%20in%20Rows%3C%2FP%3E%3CP%3ECount%20(5%2C3%2C%20etc)%20is%20in%20Values%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EJim%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-390472%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epivot%20table%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390605%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Count%20Unique%20Values%20within%20a%20Pivot%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310156%22%20target%3D%22_blank%22%3E%40JimS0422%3C%2FA%3E%26nbsp%3B%2C%20if%20creating%20PivotTable%20you%20select%20the%20option%20Add%20data%20to%20data%20model%2C%20in%20list%20of%20aggregations%20for%20values%20you%20may%20select%20Distinct%20count%20instead%20of%20Count%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

In a pivot table (attached), I would like to add a column to show a count of unique names.  Effectively for a small batch with only one territory, it is easy to do a =COUNTA for the names listed.  But how can I do this for multiple names in multiple territories within a pivot table? (~20,000 lines to analyze)  

 

Pivot Setup:

Territory (Country1) is in Rows

Name (Aaron, Adrian, etc) is in Rows

Count (5,3, etc) is in Values

 

Thank you!

Jim 

 

 

 

 

1 Reply

@JimS0422 , if creating PivotTable you select the option Add data to data model, in list of aggregations for values you may select Distinct count instead of Count