Unique distinct count in pivot table?

L

LB

I have the following table with fields "Name", "State", and "price":

Name
State
price
Mary OH 14.94
Mary OH 15.01
Joe WA 12.17
Joe WA 18.95

I create a pivot table putting State in the row area and Count of Name
in the data area. I get the following results:

State
OH
2
WA 2

This is telling me the number of occurrences of a name in the table in a
given state. What I want it to tell me is the number of different names
in a state. Is there an elegant way to do this in a pivot table? Much
thanks for any help.
 
D

Debra Dalgleish

A pivot table won't calculate a unique count. You could add a column to
the list, then add that field to the pivottable.

For example, to count unique clients per city, where Name is in
column A, and State is in column B:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($S$2:$S2=S2))>1,0,1)

Copy this formula down to all rows in the list.

In the pivot table, with State in the row area, add this new field to
the data area, and you'll get a count of unique names per state
 
Top