Home > Pivot > Unique > No Data Model
Count Unique Items in Pivot Table No Data Model

In Pivot Table A, shown below, there are 8 unique sales reps listed for the East region. In Excel 2010 pivot tables, there isn't a builtin unique count, so you can use a second pivot table (B) instead.
Note: For Excel 2013, and later versions, I recommend the data model unique count method.


Contents
 Pivot Table Unique Count
 Build Two Pivot Tables
 Download Sample Workbooks
 Related Tutorials
Pivot Table Unique Count
To get a unique count of salespeople per region in Excel 2010, you can
use two pivot tables, instead of one.
 First, create a pivot table summary
 Next, create a second pivot table, based on the first one.
Thanks to Roger Govier, who shared this
technique and workbook.
Product Sales Data
In this example, there is a named Excel table, with product
sales records. Each record shows the region and salesperson name, and other data.
Note: You can get the sample file in the Download section below.
Build Two Pivot Tables
The four steps below show how to build the two pivot tables, and get a distinct (unique) count for the Person field
Step 1) Create First Pivot Table
The first pivot table will be set up to look like a normal table of data.
To create the first pivot table, follow the steps below:
 Create a Pivot Table from the sales data, with Region and Person in
the Rows area
 Add Units and Value in the Values area
 Because Person is a text
field, it will automatically show as "Count
of"
 Format the pivot table with the Tabular report layout
 Set all the Item labels to repeat in each row.
 Change the column headings, to remove the "Sum of"
 Tip: Type the original field name, with a space character at the
end
 (Optional) Name the pivot table, PT_A
Step 2) Name Pivot Table Range
In Step 4, you'll add a macro to your workbook, to refresh the pivot tables automatically. That macro creates a named range, myData, where the first pivot table (PT_A) is located.
For now, follow the quick steps below to manually create that named range:
 In the first pivot table (PT_A), select the Region heading cell
 Press the keyboard shortcut, Ctrl+A, to select all the heading and data cells
 Next, click in the Name box, at the left of the Formula bar
 Type a oneword name for the cell  myData
 Finally, press Enter, to complete the name
Note: If you don't want to use macros, go to the No Macros section. There are steps for manually setting up the myData named range.
Step 3) Create Second Pivot Table
Next, follow the steps below, to create the second pivot table:
 Insert a new Pivot Table, based on the myData named range
 In the empty pivot table, put Region in the Row area
 Put Person, Units and Value in the Values
area
 (Optional) Name the pivot table, PT_B
 (Optional) Remove "Count of" and "Sum of" from the headings
Because it is based on the summarized data in the first pivot table (PT_A), this pivot table (PT_B) shows the count of unique salespeople per regions.
In the screen shot below, the East region shows a count of the 8 unique salespeople's names.
Step 4) Refresh Pivot Tables
Any time you change the source data, you need to refresh both pivot tables, manually, or with a macro.
 Macro: I recommend using the following macro for this, so the pivot tables automatically show the latest data from the source table.
 No Macro: If you don't want to use macros, go to the No Macros section. There are steps for manually refreshing the pivot tables.
4a) Add Pivot Refresh Macro
To add the macro to your workbook, follow the steps below:
 First, copy the macro code shown in the box below (select all the text, and press Ctrl+C )
 Next, in your workbook, rightclick the tab for sheet where the second pivot table (PT_B) is located.
 In the popup menu, click View Code
 In the code module that opens, rightclick where the cursor is flashing
 Click the Paste command
Macro Code
Private Sub Worksheet_Activate()
Dim Sh1Name As String
Dim Pt1Name As String
Dim Pt2Name As String
'sheet where 1st pivot located
Sh1Name = "TwoPivots"
Pt1Name = "PT_A" 'first pivot
Pt2Name = "PT_B" 'second pivot
Sheets(Sh1Name).PivotTables(Pt1Name) _
.PivotCache.Refresh
ThisWorkbook.Names.Add _
Name:="myData", _
RefersTo:=Sheets(Sh1Name) _
.PivotTables(Pt1Name) _
.TableRange1
Me.PivotTables(Pt2Name) _
.PivotCache.Refresh
End Sub
4b) Complete the Code
To complete the code, follow these two steps:
 First, if your workbook has different pivot table names, and sheet name, change the code to use your workbook's names.
 After that, press the keyboard shortcut, Alt+Q, to close the Visual Basic Editor, and return to Excel.
4c) Test the Code
After you follow the steps below, to add the macro code, the two pivot tables will refresh automatically, any time you go to the second pivot table's sheet.
To test the code, follow these steps:
 Go to the source data table
 Make a big change to one of the numbers. For example, change the first Unit from 1 to 10000
 Go to the second pivot table sheet
 The pivot tables should automatically update, and the larger number of units shows in the Units column
Two Pivots  No Macros
If you don't want macros in your workbook, follow the steps below to:
 A) Use a formula to create the myData named range
 B) Refresh both pivot tables manually, in correct sequence
A) Create myData Named Range
The myData named range includes all the headings and data in the main part of the first pivot table (PT_A)
For a nomacro solution, follow the 3 steps below, to set this up:
Note: The myData named range will update automatically, after you refresh the first pivot table.
Step 1) Name Pivot Start Cell
First, follow these steps to name the first cell in the pivot table.
 In the first pivot table (PT_A_, select cell B2  heading cell in the pivot table's Region
column
 Next, click in the Name box, at the left of the Formula bar
 Type a oneword name for the cell  pvtStart
 Finally, press Enter, to complete the name
Step 2) Name Pivot End Row Number
Next, follow these steps to name a cell that calculates the last row number in the pivot table:
 On the Data sheet, in cell M2, enter this formula:
 =MATCH(9.9E+307,TwoPivots!D:D,1)
 Next, select cell M2, and in the Name Box, type PvtEnd,
and press Enter.
How the Formula Works
In the MATCH function, there are 3 arguments:
 First argument is a very large number, written in scientific notation  9.9E+307
 Second argument is where to look for that number  worksheet named TwoPivots, in column D (Units)
 Third argument is 1 (Less Than)
All of the numbers in column D are less than the large number, so MATCH returns the row number of the last number in column D
Note: This
formula will only work in a column with numbers.
Step 3) Name Pivot Table Range
Next, follow the steps below, to create a dynamic named range, based on the pivot table headings and data.
This range will start in Region heading cell (pvtStart), and end in the last row in column E
(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 INDEX formula:
 =pvtStart:INDEX(TwoPivots!$E:$E, PvtEnd)
 Click OK, to create the named range
B) Manually Refresh Pivot Tables
After you make changes to the source data, be sure to refresh both pivot tables, in the correct sequence:
 Always refresh the first pivot table first
 The named range, myData, will automatically adjust
 After that, refresh the second pivot table
Other Ways to Get Unique Count
Here are three more ways to get the number of unique values in an Excel pivot table. The sample files for the COUNTIF and Power Pivot methods are in the download section below.
Data Model: For Excel 2013, and later versions, I recommend the data model unique count method.
COUNTIF: In Excel 2007 and older versions of Excel, add a column to the source data, and use the COUNTIF function. This method can cause the workbook to calculate slowly.
Power Pivot: If the Power Pivot addin is installed, use it to show a count of distinct values.
Get the Sample Files
 2 Pivots  No Macros: Download the sample Excel workbook, which contains the sample data and pivot tables. The file is zipped, and in xlsb format. The file does not contain macros.
 2 Pivots  Macros: Download the sample Excel workbook, which contains the sample data and pivot tables, and the macros to refresh the pivot tables. The file is zipped, and in xlsb format. The file contains macros, so be sure to enable them when you open the file.
 COUNTIF: For Excel 2007, which doesn't have "Repeat Labels", you can download the COUNTIF technique sample file. The file is zipped, and in xlsx format, and does not contain macros.
 Power Pivot: To follow along with the Power Pivot video, download the sample file that was used for this video
Where to Go Next
Unique Count Excel 2013
Calculated Items vs Calculated Fields
Calculated Item Examples
Calculated Field  Count
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
Roger Govier