Can you help me understand what I am doing wrong?

The suggested formula is an array formula,
and from Excel Help (xl97) for calculated field syntax:
".. you cannot use array functions. "

But you could try using the formula directly on the source data set
using a dynamic range (Cust) for the customers

Suppose the source data is in Sheet1,
with Customers listed in col B, B2 down
(the listing in col B is assumed continuous,
without any in-between blank rows)

Click Insert Name Define
Under Names in workbook, enter: Cust
In the "Refers to" box, put:
=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,)
Click OK

(The above will create a dynamic range: Cust)

In Sheet2 (say),
copy and paste into the formula bar for say, B2:

=SUM(IF(LEN(Cust)0,1/COUNTIF(Cust,Cust)))

Remember to array-enter the formula
by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

(if you don't array-enter, you'll get the error #VALUE!)

B2 should return the unique counts of the customers in col B of Sheet1

And if the counts are out, the problem probably lies with the source data
(not clean)

You could try using TRIM() to clean up any extraneous white spaces (not
apparent)

E.g.: In Sheet1, use an empty col to the right,
say col X?, put in X2: =TRIM(B2)
copy down, then copy col X and
overwrite col B with a paste-special values ok

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Mike Struckman" wrote
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