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:

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:

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

Related posts:

Count unique distinct values in a pivot table in excel

Extract a unique distinct list by matching items that meet a criterion in excel 2007 using pivot table

Count unique distinct records in excel 2007

Excel table: Filter unique distinct rows and use additional filters at the same time

List people with the highest scores based on criteria in a pivot table (Excel 2007)