Table of Contents

  1. Count unique distinct records
  2. Count duplicate records

Excel 2007 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)

Excel 2007 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 sample file for this tutorial
Pivot table Count unique distinct records.xlsx
(Excel 2007/2010 Workbook *.xlsx