How do I count unique items in a pivottable data field.

[Jaywestern]

I have a pivottable and I am trying to setup a data field that will count
unique values.

ie. Listing customers as the row and setting up a field to count the number
of sales people that sold to the customer. The base data has multiple rows
identifying the customer, sale id, $ and sales person. If I have 100 rows
with sales person A, B & C each selling multiple orders to several customers
I want the field counter to tell me if 1,2 or 3 sales people sold to each
customer.

 

[bpeltzer]

Not the most elegant answer, but I'd probably create my pivot with Customer
in the row field, sales rep in the column field, and sales rep in the data
field. The data item will turn into Count of Sales Rep. Let Excel complete
the Pivot. Then, in each row, to the right of the Pivot Table, use the
counta function. Suppose the first row of data in the pivot table is row 5
and it ends with row totals in column G. Then in H5, =counta(b5:f5) will
tell you how many unique reps sold to that customer. Autofill that formula
down through the remaining rows.

 

[Dave Peterson]

Debra Dalgleish has some tips at:
http://contextures.com/xlPivot07.html#Unique