Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel 2007 Pivot Count Distinct Values

Posted on 2010-08-31
Medium Priority
2,235 Views
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?
0
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

Author Comment

ID: 33571925
Another image.
Excel-ManualCount.jpg
0

LVL 17

Accepted Solution

calacuccia earned 1000 total points
ID: 33572713

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

J79123 earned 1000 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

Question has a verified solution.

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

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 …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month4 days, 14 hours left to enroll