Count Unique Items in Pivot Table

How to count unique items (count distinct) in an Excel pivot table. Three methods, for different versions of Excel. Get the free workbook to follow along

Thanks to Roger Govier, who created this tutorial and workbook.

Unique Count

If you're using a pivot table to summarize your data, you might have questions such as:

• How many unique (distinct) customers made purchases in each region?
• How many unique products were sold in each store?
• How many unique salepeople sold each product per region?

There isn't a built-in "Unique Count" feature in a normal pivot table, but in this tutorial, you'll see how to get a unique count of items in a pivot table.

• In Excel 2013 and later, create a Data Model
• In Excel 2010, and later versions, use a technique that "Pivot the Pivot table".
• Or, in older versions, add a new column to the source data, and Use CountIf.
• If the Power Pivot add-in is installed, use it to show a distinct count

Introduction

Often we have large data sets that we want to analyse with the aid of a Pivot Table, but we only want to count the unique occurrences of some items of data. Unfortunately, Pivot Tables have never had the inbuilt facility to count unique values.

In this example, the sample file has 4999 records that show product sales, with the region and salesperson name. The first few records are shown in the screen shot below. You can download the sample file at the link below.

Creating a Pivot Table from the data would give us something like the following, if we added Person to the Values area of the Pivot table and summarised it with Count.

However, this is giving us a count of all the transactions, not the count of the unique number of people who made those transactions within each Region.

Get a Unique Count

There are workarounds that you can use, to get a unique count:

• Method 1: In Excel 2013 and later, add the pivot table's source data to the Data Model, and a unique count can be done easily.
• Method 2: In Excel 2010 and later, use the "pivot a pivot" technique.
• Method 3: In older versions of Excel, add a column to the source data to place a 1 in cells on a row where the value is unique, and a 0 in any other cells. Then, summing this additional column provides the unique value.
• Method 4: Use PowerPivot to create the pivot table, and use its functions to create a unique count. See the details below.

All four methods are described below, along with a performance comparison between methods 2 and 3.

1. Add to Data Model - Excel 2013 and Later

In Excel 2013, if you add a pivot table's source data to the workbook's Data Model, it is easy to create a unique count.

NOTE: This technique creates an OLAP-based pivot table, which has some limitations, such as no grouping, and no calculated fields or calculated items. If you need these restricted features, try the "pivot a pivot" method instead.

Create a Unique Count (Distinct Count)

To create the pivot table, follow these steps:

• Select a cell in the source data table.
• At the bottom of the Create PivotTable dialog box, add a check mark to "Add this data to the Data Model"
• Click OK

To set up the pivot table layout, follow these steps:

• In the pivot table, add Region to the Row area.
• Add these 3 fields to the Values area -- Person, Units, Value
• The Person field contains text, so it defaults to Count of Person. The count shows the total number of transactions in each region, not a unique count of people

To get a unique count of people in each region, follow these steps:

• Right-click one of the values in the Person field
• Click Value Field Settings

• In the Summarize Value Field By list, scroll to the bottom, and click Distinct Count, then click OK

The Person field changes, and instead of showing the total count of transactions, it shows a distinct count of salespeople's names.

2. Pivot the Pivot - Excel 2010

To get a unique count of salespeople per region in Excel 2010, we can produce a Pivot Report, and then "Pivot the Pivot" to get our final result.

Create the First Pivot Table

• Create a Pivot Table from this data, with Region and Person in the Rows area
• Add Units and Value in the Values area. Because Person is a text field, the Pivot table will automatically show it as "Count of".
• Format the pivot table with the Tabular report layout
• Set all the Item labels to repeat in each row.
• Change the Captions, to remove the "Sum of"
  TIP: Type the original field name, with a space character at the end, e.g. "units "

This now looks like a normal table of data, but it is a summary of the original full data table with 4,999 rows.

Create a Named Range for the Start Cell

Next, you'll name the first cell in the pivot table.

• Select cell B2, which is the heading in the pivot table's Region column
• Click in the Formula Bar, and type a name for the cell -- pvtStart
• Press Enter, to complete the name

Create a Named Range for the End Row

The next step is to calculate the location of the last row in the pivot table. We'll use the MATCH function, with a very large number, to find the last number in column D, and get its row number. This formula will only work in a column with numbers.

• On the Data sheet, in cell M2, enter this formula, to calculate the last row in the pivot table's Units column:

  =MATCH(9.9E+307,Method_1!D:D,1)

• To name that cell, select cell M2, and in the Name Box, type PvtEnd, and press Enter.

Create a Dynamic Named Range

Next, we will create a dynamic Named Range, to refer to this new table. This range will start in the cell named pvtstart (the Region heading cell), and end in the last row of data in column E (the named range, PvtEnd).

• On the Ribbon's Formulas tab, click Define Name
• In the New Name dialog box, enter the name, myData
• In the Scope drop down, select Workbook
• In the Refers To box, type this formula:
  =pvtStart:INDEX(Method_1!$E:$E, PvtEnd)
• Click OK, to create the named range
  

Create the Second Pivot Table

Finally, create a second Pivot Table, based upon the dynamic range, myData.

Put Region in the Row area, and Person, Units and Value in the Values area

This shows the number of unique salespeople per regions as shown below. For example, in the first pivot table, the East region showed 8 unique names in the person column, and that is the count in this pivot table.

Refresh the Pivot Tables

Naturally, as there are 2 Pivot Tables involved in this solution, both have to be refreshed after any data has been added or changed in the source table. It is essential that the first PT is refreshed first, followed by a Refresh of the Final Pivot Table.

You can refresh the pivot tables manually, or use amacro. To use a macro, add one of the following procedures to the sheet with your final Pivot Table.

A) If both pivot tables are on the same sheet, use this code:

Private Sub Worksheet_Activate() 

    Me.PivotTables(1).PivotCache.Refresh 

    Me.PivotTables(2).PivotCache.Refresh 

End Sub

B) If the pivot tables are on different sheets then use this code (substitute the name of your actual sheet for "yoursheetname"):

Private Sub Worksheet_Activate() 

    Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh 
    Me.PivotTables(1).PivotCache.Refresh 
End Sub

(Optional) Name the Pivot Table Data Range

If you are using macros to refresh the pivot tables, you could also use a macro to reset the myData named range after each refresh. Add the following procedure to your workbook, then call this SetmyData procedure in the Worksheet_Activate procedures.

Sub SetmyData()
'Change "PT_A" to whatever name
'you have given to your first Pivot Table
'that is to be used as the source
'for for the final Pivot Table

'Also change the sheet name
'from "Method_1" to
'your sheet name if it is different

Dim PTName As String
Dim ShName As String

PTName = "PT_A"
ShName = "Method_1"

ThisWorkbook.Names.Add _
    Name:="myData", _
    RefersTo:=Sheets(ShName) _
        .PivotTables(PTName) _
        .TableRange1
End Sub

3. Use COUNTIF -- Excel 2007 and earlier

In earlier versions of Excel, pivot tables don't have the option of repeating row labels. To get a unique count of salespeople per region, you can add a new column in the source data, and enter a COUNTIF formula

You can download the sample file at the link below.

Add the COUNTIF Formula

Follow these steps, to add the formula:

In the source data, add a new column heading -- "Unique" -- in cell J1

In cell J2, enter this formula, and copy it down to the last row of data:

=IF(COUNTIF($E$5:E5,E5)>1,0,1)

The first reference in the range ($E$5) is Absolute, and the second is relative (E5), so as we copy the formula down the page, so the range will expand from $E$5:E5 to $E$5:E6, then $E$5:E7 and so on.

In each row we are checking how many times the value in column E (Person) has occurred, down to the current row. If it is greater than once, the result is zero, so that person is not counted again. This lets us see the number of unique times within the dataset that each Person's name has appeared.

With the extra column added, the data would look like this. The second instance of "Harry" returns a zero in the Unique column.

NOTE: If there are people of the same name selling in different Regions:

• In Excel 2007 or later: Use COUNTIFS, to check multiple criteria. For example, =IF(COUNTIFS($D$5:$D5,D5,$E$5:E5,E5)=1,1,0)
• In Excel 2003 or earlier, concatenate the Region and Person names in a new column, and then use the COUNTIF formula to find Unique values in that concatenated column.

Create a Pivot Table

To see the unique counts in a pivot table:

• Create a Pivot Table from this data, with Region in the Rows area
• Add Unique, Units and Value in the Values area.
• Change the "Unique" heading to "Count of Person" or "Person "

We can see that there are 30 people who made the Sales, and the number of each who are in each Region, and if we chose to expand any Region then we would see the individual totals for each Person.

Performance Comparison

If possible, use the "pivot a pivot" method, which is much faster.

COUNTIF Method

Using a COUNTIF formula in the source data works, and gives us our desired result, and on a relative small dataset the method is acceptable. But, it is very costly in terms of processing time, and on very large datasets can be extremely slow.

Using Charles Williams' "FastExcel" to calculate the time it takes for the workbook to calculate gives the result shown below, where the time taken to recalculate the Data sheet is 224 milliseconds.

Pivot a Pivot Method

The "Pivot a Pivot" method also produced the correct result, with a unique count of Person, as required. In addition, it does not rely upon additional columns in the source data, nor entering of any new formulae.

The resulting workbook is smaller, and again using Charles Williams' "FastExcel" to calculate the time for calculation of the Data sheet is an incredibly fast 1.2 milliseconds

With small datasets timing of recalculation may not be critical, but when dealing with larger datasets of 300,000 to 400,000 rows, then these differences would become very material.

4. Power Pivot

If you have the PowerPivot add-in installed, you can use it to show a distinct count for a field. This video shows the steps for creating a Power Pivot pivot table, and adding a field with the unique count.

For written instructions, see the Excel 2013 steps on my pivot table blog. There are instructions for Excel 2010 too.

To follow along with the video, download the sample file that was used for this video

Download the Sample File

Download the sample workbook, which contains examples for Method 2 (pivot a pivot) and Method 3 (column in source data). The file is zipped, and in xlsb format. The file does not contain macros.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

Related Tutorials

Naming Ranges

Count Unique in PowerPivot

Sample Files - Roger Govier

 

 

About the Developer

Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.

Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier

You can contact Roger at:

roger@technology4u.co.uk
Technology 4 U, Glanusk Farm,
Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK
Tel +44 (0) 1873 880266
Mobile +44 (0) 7970 786191

Roger Govier