Solved

Excel 2007 Pivot Count Distinct Values

Posted on 2010-08-31
3
2,017 Views
Last Modified: 2012-05-10
Hello Excel Gurus!
I have an excel sheet with 17k rows.  I am trying to understand excel pivot tables and want to know if I can use the pivot to display the count of distinct values for one of my columns.  
 
So far, I did use the excel function (=SUMPRODUCT((D2:D17211<>"")/COUNTIF(D2:D17211,D2:D17211&""))) to calculate the count and it worked.  This unfortunately does not tell me how to arrive at the same answer in a pivot table.

Can someone tell me how to calculate the count of distinct values and have it display it on my pivot? Excel Function Works Unique
0
Comment
Question by:BajanPaul
3 Comments
 

Author Comment

by:BajanPaul
Comment Utility
Another image.
Excel-ManualCount.jpg
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 250 total points
Comment Utility
I believe your only option is a workaround with an additional column in your source data

http://www.contextures.com/xlPivot07.html


Count Unique Items

In a pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable.

For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'

In the first data row, enter a formula that refers to the customer and item columns. For example:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

Copy the formula down to all rows in the database.

Then, add the field to the data area of the pivot table
0
 
LVL 6

Assisted Solution

by:J79123
J79123 earned 250 total points
Comment Utility
double click the field you need the unique count of (Order)
choose Count
then click options and select index.

Index gives you just one count for each value, uniques.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now