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.**

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.

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.

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.

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

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 ability to add calculated fields or calculated items. If you need the restricted features, try the "pivot a pivot" method instead.

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.

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 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.

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

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.

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

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.

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

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

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.

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 differing Regions, then we might first need to carry out a concatenation of Region and Person first into a new column, and then carry out the formula for finding the Unique values upon the new concatenated column.

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.

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

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.

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.

Download the sample workbook, which contains both methods. The file is zipped, and in xlsb format. The file does not contain macros.

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!

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**

Last updated: October 26, 2017 3:41 PM