Improve company productivity with a Business Account.Sign Up

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2393
• Last Modified:

Excel 2007 Pivot Count Distinct Values

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
BajanPaul
Asked:
2 Solutions

Author Commented:
Another image.
Excel-ManualCount.jpg
0

Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.