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
- 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.
- 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)
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
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 […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]
The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
9 Responses to “Count unique distinct records (rows) in a Pivot Table”
Leave a Reply to Sanjoy
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!!!