A pivot table can sum and count and average, and do several other functions, but so far, it doesn’t have a Unique Count (Distinct Count) function. If you’ve built a PowerPivot pivot table, you’re in luck – it does have a DISTINCTCOUNT function.
For example, see a count of the stores in each region or city, instead of the number of records for each store.
If you’re not using PowerPivot, you can still create a Unique Count field – with a bit of work. Roger Govier has written an new tutorial on my Contextures website, to explain the steps.
There are 2 options:
- 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.
1 – Pivot the Pivot
In the first workaround, you’ll create 2 pivot tables:
- pivot table A summarizes the data, and it is formatted to show the labels in every row
- pivot table B is based on the worksheet range that contains pivot table A
- you can use a dynamic named range
- or create the named range in a macro, when the data is refreshed
2 – Add Formula in Source Data
If you’re using Excel 2007 or an earlier version, there’s no option for repeating labels in a pivot table. So, instead of the previous method, you can add a formula in the pivot table’s source data, to calculate a unique count.
In the screen shot below, you can see the COUNTIF formula that’s used in column J. It returns a 1 in the first row that a person’s name appears, and a 0 in subsequent rows for that person.
Then, in the pivot table, the “Unique” field is added, and its caption is changed to “Person “.
However, this method slows down the workbook, so use the “pivot the pivot” method if you can, for a large set of data.
Get the Details and Sample File
You can read Roger’s article for the full details, and download his sample file. The zipped file is in xlsb format, and contains an optional macro. You can manually refresh the pivot tables, if you prefer.