Unique Count in Excel Pivot Table

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.

powerpivotunique2013_15b

Non-PowerPivot Workaround

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

pivotuniquecountrg10

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.

pivotuniquecountrg11

Then, in the pivot table, the “Unique” field is added, and its caption is changed to “Person “.

pivotuniquecountrg07

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.

_______________

You may also like...

7 Responses

  1. Jay says:

    2013 Excel adds distinct count feature when creating a pivot table. Just put a check on option “add to data model”(or something like that)…no need for Power Pivot.

  2. Rudi says:

    Hi,

    You end this article saying: “However, this method shows down the workbook, so use the “pivot the pivot” method if you can.”

    Did you mean “slows” down or is that supposed to be “shows” down?
    If it is “slows” down, what might the reason be for slowing the workbook down? It not an array function, unless you are referring to a very large volume of data.

    TX Debra. :)

  3. K says:

    Why don’t you try just add count formula below or next to pivot? ;) For example pivot with a region as a columns labels, person as a rows labels, as a value anything(count of persons) and then count formula below pivot(one for each region)?

  4. Martin Sprenz says:

    Is there a way to filter the output of a pivot table where the value of Count of SelectedField is say less than or greater than some value? I know I can do this in a second step. I really want to finished pivot table to have the desired result in the first step.

    i.e.
    original:
    Susan 1
    Jack 19
    Karen 5
    Sam 42
    Rick 28
    Rebecca 22

    with Filter applied
    Count of fldName > 10
    Jack 19
    Sam 42
    Rick 28
    Rebecca 22

Leave a Reply

Your email address will not be published. Required fields are marked *