Count Unique Values in Pivot table

[Craig860]

I'm making a pivot table that counts the number of accounts#'s. Unfortunately
the account#'s are sometimes duplicated. Without altering the original data,
is there a way I can do something in my pivot table so that it only counts
unique account numbers and discards the dupes?

Thanks.
IE:

Name #of new accounts
Bob 6
Joe 3
Sam 2

 

[muddan madhu]

In pivot table select the value.

Right click | field setting | options | show data as : index | ok

 

[Craig860]

That didn't work as expected. I got a really odd number like 0.914170838
the sum should be around 600 or 700.

 

[Bernie Deitrick]

You could use a formula like

=COUNTA(A:A) - 3

where the account numbers are in the first column of your pivot table. Change the 3 depending on
your subtotal settings...

HTH,
Bernie
MS Excel MVP

 

[Craig860]

Sorry I don't follow what you want me to do.

In my pivot I got the account name in column A.
in my data I have Count of Acct#

there may be 1000 account numbers but I only wan't to count the unique
numbers which should reduce it to about 500.

 

[Bernie Deitrick]

Craig,

Excel will only put a values once into column A of your pivot table, so
using the formula COUNTA(A:A) - X will report the number of unique values.
If an account number seems to appear more than once, then it is being
entered differently somehow in the data tabel, as Excel is interpreting
those instances as being different. Perhaps you have an extra space, an
added digit, a dash, or some other misspelling in your account numbers.

HTH,
Bernie
MS Excel MVP