Using a Pivot Table Calculated Field to get a Unique Count

[Mike Struckman]

I am using a Pivot Table to view data from a very large database. The Pivot
Table has the following fields: Customer, Quarter and Sales. There are many
duplicate Customer sales in the database.

I would like to create a Pivot Table that calculates the # of unique
customers in each quarter. If I use the Pivot Table Count function it double
counts all of the duplicate Customer entries. Can I create a Calculated Field
that accomplishes this?

I have tried using this formula that I found in a previous string,
“=SUM(IF(LEN(Sheet1!A2:A100)>0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))â€,
but since I am using it in a Pivot Table calculated field I used the
following format: “=SUM(IF(LEN(Customer)>0,1/COUNTIF(Customer, Customer)))â€.
Where “Customer†is inserted from the Pivot Table field list. I get a “The
function contains an error†message.

Can you help me understand what I am doing wrong? Thanks!

 

[cs02000]

Post an email address and I'll send you what I worked on. This site
doesn't allow me to post an Excel file (go figure)