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)
Pivot table category
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 count unique […]
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 […]
Excel categories
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