Solved

Excel 2007 Pivot Count Distinct Values

Posted on 2010-08-31
3
2,069 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
ID: 33571925
Another image.
Excel-ManualCount.jpg
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 250 total points
ID: 33572713
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
ID: 33583941
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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