Author: Oscar Cronquist Article last updated on January 22, 2019

Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a helper formula for earlier Excel versions.

Table of Contents

  1. Count unique distinct records
  2. Count duplicate records

Pivot table: Count unique distinct records (rows)

The table I am working with is in cell range B2:D7. I have then added another column (E) to count unique distinct rows.
Setup table
Formula in cell E3:

=1/COUNTIFS($B$3:$B$7, B3, $C$3:$C$7, C3, $D$3:$D$7, D3)

Copy cell E3 and paste down to E7.


Create pivot table

  1. Select cell range B2:D7
  2. Click "Insert" tab on the ribbon
  3. Click "Pivot table" button
  4. Choose where you want the pivot table to be placed.
  5. Click OK.

Setup pivot table

  1. Click and drag Name, Address and City to row labels.
  2. Click and drag Count to values.

There are three unique distinct rows (Grand Total)

Pivot table: Count duplicate records (rows)

The table I am working with in this example is in cell range B2:D12. I have then added another column (E) to count duplicate rows.

Setup table

Formula in cell E3:

=1-(1/COUNTIFS($B$3:$B$12, B3, $C$3:$C$12, C3, $D$3:$D$12, D3))

Copy cell E3 and paste down to E12.


Create pivot table

  1. Select cell range B2:D12
  2. Click "Insert" tab on the ribbon
  3. Click "Pivot table" button
  4. Choose where you want the pivot table to be placed.
  5. Click OK.

Setup pivot table

  1. Click and drag Name, Address and City to row labels.
  2. Click and drag Count to values.

There are six duplicate rows (Grand Total)

Download Excel file


Pivot-table-Count-unique-distinct-records.xlsx

Related articles