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.
- Select cell range B2:D7
- Click "Insert" tab on the ribbon
- Click "Pivot table" button
- Choose where you want the pivot table to be placed.
- Click OK.
Setup pivot table
- Click and drag Name, Address and City to row labels.
- 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:
Copy cell E3 and paste down to E12.
- Select cell range B2:D12
- Click "Insert" tab on the ribbon
- Click "Pivot table" button
- Choose where you want the pivot table to be placed.
- Click OK.
Setup pivot table
- Click and drag Name, Address and City to row labels.
- Click and drag Count to values.
There are six duplicate rows (Grand Total)
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!!!