Count unique distinct records (rows) in a Pivot Table
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
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
- Select cell range B2:D7
- Press with left mouse button on "Insert" tab on the ribbon
- Press with left mouse button on "Pivot table" button
- Choose where you want the pivot table to be placed.
- Press with left mouse button on OK.
Setup pivot table
- Press with left mouse button on and drag Name, Address and City to row labels.
- 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
- Select cell range B2:D12
- Press with left mouse button on "Insert" tab on the ribbon
- Press with left mouse button on "Pivot table" button
- Choose where you want the pivot table to be placed.
- Press with left mouse button on OK.
Setup pivot table
- Press with left mouse button on and drag Name, Address and City to row labels.
- Press with left mouse button on and drag Count to values.
There are six duplicate rows (Grand Total)
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Count unique distinct records with a date and column criteria
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
Filter unique distinct records with a condition
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Unique distinct records sorted based on count or frequency
Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
9 Responses to “Count unique distinct records (rows) in a Pivot Table”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
...all ok with exampke untill the moment of questions: if for e.g. i have two uniques values with the same amount of records.. then dividing will be nothing worse....
..."nothing worth" i have meant (sorry :) )
Andrius,
Yes..
I am not sure I understand.
Hello,
I did automate the process in a vba macro that add a unique count datafield in the pivot, automatically updated when you change the configuration of the pivot.
Let me know your thoughts.
https://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html
lazyvba,
I did try your code and it seems to work with my example pivot table!
It converted my data to a table and added new columns, maybe you mentioned that on your website?
Thanks a lot! This solution saved me some hours.
Ben,
Thanks for commenting!
Boss you are genius. Hv been searching this for quite some time.
This is amazing and saved me a lot of work. THANKS!!!