Solved

Excel 2007 Pivot Count Distinct Values

Posted on 2010-08-31
3
2,167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

635 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