Count unique distinct values in an Excel Pivot Table
I have a small problem that I am not sure on how to solve.
I now have a list of 15 cells with 3 unique values (arr formula in all 15 cells). When I use this into a pivot table, I want to get the count as 3 since that is the unique count. But I am getting 15 as the value!
I am assuming that the pivot table is reading the formula as a value and counting it. Any way to circumvent that?
Answer:
Update: It is now possible to count unique distinct values in a Pivot Table. You need Excel 2013 or a later version.
I don´t think it is possible to count unique values in a pivot table unless you add another column to your table or use VBA: Improve your Excel Pivot Table to count unique values or items
Formula in D3:
Copy cell D3 (Ctrl + c). Paste (Ctrl+ v) on cell range D4:D17.
Setup pivot table
- Press with left mouse button on and drag Array formula to Row Labels
- Press with left mouse button on and drag Count to Values
How the formula in cell D3 works
Step 1 - Construct the COUNTIF function
COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition.
Select cell D3.
Type =COUNTIF(
Select cell range C3:C17
Press F4. You have now created absolute cell references to cell range C3:C17.
=COUNTIF($C$3:$C$17
Now create a relativ cell reference to cell C3.
=COUNTIF($C$3:$C$17, C3)
=COUNTIF($C$3:$C$17, C3)
becomes
=COUNTIF({"AA"; "AA"; "AA"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "CC"; "CC"; "CC"; "CC"}, "AA")
and returns 3. There are three AA in the array.
Step 2 - Divide 1 with the number of counted values
=1/COUNTIF($C$3:$C$17, C3)
becomes
=1/3
and returns
0.333333333333333 in cell D3.
Count values in a pivot table using an Excel defined table as a data source
Recommended blog posts
Excel 2007 pivot table: Count unique distinct records (rows)
Weekly Blog EMAIL
[newsletter_signup_form id=1]
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
Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Create a drop down list containing alphabetically sorted values
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Populate a combo box (form control) [VBA]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
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.
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 […]
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
4 Responses to “Count unique distinct values in an Excel 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.
This is a simple and beautiful solution! Thanks!
great idea! thank you
Hey Thank you so much, really effective and simple solution.
thanks for the help