Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 the first  screenshot shown, but,sometimes, we just want to count the unique values based on one column to get the second 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 in Excel 2007/2010 Count unique values in pivot table with Value Field Settings in Excel 2013
doc-count-unique-pivottable-1 doc-count-unique-pivottable-1

Advanced Combine Rows(Combine duplicate rows and merge or do some calculations of corresponding values ):

With Kutools for Excel’s Advanced Combine Rows utiltiy, you can quickly combine multiple duplicate rows into one record based on key columns, and it also can apply some calculations such as sum, average, count and so on for other columns.

1. Specify the key column that you want to combine other column based on;

2. Choose the separator or other calculations for your combined data.

doc advanced combine rows 1

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Count unique values in pivot table with helper column in Excel 2007/2010


In Excel 2007 or 2010, 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, see screenshot:

doc-count-unique-pivottable-1

2. 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 follows:

doc-count-unique-pivottable-1

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

doc-count-unique-pivottable-1

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

doc-count-unique-pivottable-1

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

doc-count-unique-pivottable-1


arrow blue right bubble Count unique values in pivot table with Value Field Settings in Excel 2013

In Excel 2013, a Distinct Count function has been added in the pivot table, you can easily apply this feature.

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 check box, see screenshot:

doc-count-unique-pivottable-1

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:

doc-count-unique-pivottable-1

3. And then click the drop down list from Values, choose Value Field Settings, see screenshot:

doc-count-unique-pivottable-1

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

doc-count-unique-pivottable-1

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

doc-count-unique-pivottable-1


Related articles:

How to insert page break into pivot table?

How to show multiple grand totals in pivot table?



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Dilip · 7 months ago
    Distinct count Option not shown in summarize value by - Excel version 2013
    • To post as a guest, your comment is unpublished.
      Karim Masarweh · 5 months ago
      Please verify that you have ticked the "Add this data to data model" check in the CreatePivot dialog box :)
    • To post as a guest, your comment is unpublished.
      Karim · 5 months ago
      I faced the same issue and then found the resolution.
      Seems that it's available only when you tick the "Add this data to the Data Model" checkbox in the Create PivotTable dialog box.
      Please try if that helps
    • To post as a guest, your comment is unpublished.
      Julio · 6 months ago
      same for me! Any suggestion?
  • To post as a guest, your comment is unpublished.
    Brian Vaughn · 8 months ago
    These all work but only to an extent. I'm trying to find a solution for the issue with all of these. When I create a helper column and use the formula =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) I do indeed get the distinct count. But how do you resolve the issue were you need the pivot fields to include one of the lines of data where the formula gives a zero? I also tried using the Data Model and distinct count. This gives the correct count but when you double click the data to drill down you do not get the data specified in the pivot.
  • To post as a guest, your comment is unpublished.
    Ray Man · 8 months ago
    Amazing! thanks a tons - this worked for me on Excel 2016.
  • To post as a guest, your comment is unpublished.
    Guest · 9 months ago
    I don't see the Distinct Count under Summarize Value By tab. My "Add this data to the Data model" check box is also grey out. How can I change this setting?
  • To post as a guest, your comment is unpublished.
    Ms Nadia · 9 months ago
    omg!!! yes...thanks for this!!!!