## How to count unique values in pivot table?

By default, when we create a pivot table based on a range of data which contains some duplicate values, all the records will be counted as well, but, sometimes, we just want to count the unique values based on one column to get the right screenshot result. In this article, I will talk about how to count the unique values in pivot table.

Count unique values in pivot table with helper column

Count unique values in pivot table with Value Field Settings in Excel 2013 and later versions

#### Count unique values in pivot table with helper column

In Excel, you need to create a helper column to identify the unique values, please do with the following steps:

1. In a new column besides the data, please enter this formula =IF(SUMPRODUCT((\$A\$2:\$A2=A2)*(\$B\$2:\$B2=B2))>1,0,1) into cell C2, and then drag the fill handle over to the range cells that you want to apply this formula, and the unique values will be identified as below screenshot shown:

2. Now, you can create a pivot table. Select the data range including the helper column, then click Insert > PivotTable > PivotTable, see screenshot:

3. Then in the Create PivotTable dialog, choose a new worksheet or existing worksheet where you want to place the pivot table at, see screenshot:

4. Click OK, then drag the Class field to Row Labels box, and drag the Helper column field to Values box, and you will get the following pivot table which just counts the unique values.

#### Count unique values in pivot table with Value Field Settings in Excel 2013 and later versions

In Excel 2013 and later versions, a new Distinct Count function has been added in the pivot table, you can apply this feature to quickly and easily solve this task.

1. Select your data range and click Insert > PivotTable, in the Create PivotTable dialog box, choose a new worksheet or existing worksheet where you want to place the pivot table at, and check Add this data to the Data Model checkbox, see screenshot:

2. Then in the PivotTable Fields pane, drag the Class field to the Row box, and drag the Name field to the Values box, see screenshot:

3. And then click the Count of Name drop down list, choose Value Field Settings, see screenshot:

4. In the Value Field Settings dialog, click Summarize Values By tab, and then scroll to click Distinct Count option, see screenshot:

5. And then click OK, you will get the pivot table which count only the unique values.

• Note: If you check Add this data to the Data Model option in the Create PivotTable dialog box, the Calculated Field function will be disabled.

#### More relative PivotTable articles:

• Apply The Same Filter To Multiple Pivot Tables
• Sometimes, you may create several pivot tables based on the same data source, and now you filter one pivot table and want other pivot tables are filtered with the same way as well, that means, you want to change multiple pivot table filters at once in Excel. This article, I will talk about the usage of a new feature Slicer in Excel 2010 and later versions.
• Update Pivot Table Range In Excel
• In Excel, when you remove or add rows or columns in your data range, the relative pivot table does not update at the same time. Now this tutorial will tell you how to update the pivot table when rows or columns of the data table change.
• Hide Blank Rows In PivotTable In Excel
• As we know, pivot table is convenient for us to analyze the data in Excel, but sometimes, there are some blank contents appearing in the rows as below screenshot show. Now I will tell you how to hide these blank rows in pivot table in Excel.

### Best Office Productivity Tools

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!