Contextures

Count Unique Items in Pivot Table

in this tutorial, you'll see how to get a unique count of items in a pivot table.

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, but in this tutorial, you'll see how to get a unique count of items in a pivot table.

  • In Excel 2010, and later versions, use a technique that "pivots the pivot table".
  • Or, in older versions, add a new column to the source data, and use it to calculate the unique 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.

source data for pivot

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.

pivot table with person 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 2: In older versions of Excel, you can add another 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 1: Thanks to the new features in Excel 2010 and later, there is a better technique now, which I call "pivot a pivot".

Both methods are described below, along with a performance comparison..

1. Pivot the Pivot - Excel 2010 and Later

To get a unique count of customers 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 "

pivot table

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

pivot table

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

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

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

source data with COUNTIF

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.

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 "

Pivot Table with Unique field

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.

recalculation time

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

recalculation time

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 File

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

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 Roger Govier

Technology 4 U

Related Tutorials

Search Contextures Sites

 

pivot power premium

 

 

pivot power premium

 

 

Last updated: September 25, 2016 7:03 PM