I have been struggling with this same problem, but I need to find a solution
that works in a Pivot Table calculated field...

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 the formula you provided,
€œ=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

"Marisa" wrote:

I have the data source like below:
product customer quantity
1 A 1000
2 A 1000
1 B 500
3 B 200

and my pivot table like below:
customer product total
A 1 1000
2 1000
A Count 2
B 1 500
3 200
B Count 2

How can I get the number of customer = 2 (A, B)?
and number of product = 3 (1, 2, 3)?
It seems to me that the count on pivot table only count the number of row of
that data.

Can I get this count from pivot table or other excel formula?