Using a Pivot Table Calculated Field to get a Unique Count
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!
--
Mike Struckman
|