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:

Copy cell E3 and paste down to E7.

Create pivot table

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

Setup pivot table

1. Press with left mouse button on and drag Name, Address and City to row labels.
2. Press with left mouse button on 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:

Copy cell E3 and paste down to E12.

Create pivot table

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

Setup pivot table

1. Press with left mouse button on and drag Name, Address and City to row labels.
2. Press with left mouse button on and drag Count to values.

There are six duplicate rows (Grand Total)

Get the Excel file

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

Related articles

How to save custom functions and macros to an Add-In
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]