Excel pivot table counting problem

J

jca

I have a list of transaction in an excel sheet. It looks like this:

Client #, City, Transaction
125 A 125148
125 A 125168
125 A 125198
135 A 125154
135 A 125158
135 A 126120
148 B 126487
148 B 127845

I'm trying to do a pivot table on this list to show me how many clien
there is in each city. So, city A should have 2 clients and city B 1.
But I can't seem to get it working, my pivot table is showing me 6 fo
city A and 2 for city B. How can I get it to work without changing m
list
 
D

Debra Dalgleish

A pivot table won't calculate a unique count, so you may have to change
your list. You could add a column to the list, then add that field to
the pivottable.

For example, to count unique clients per city, where client number is in
column C, and City name is in column D:
=IF(SUMPRODUCT(($C$2:$C2=C2)*($D$2:$D2=D2))>1,0,1)

Copy this formula down to all rows in the list.

In the pivot table, with Client# and City in the row area, add this new
field to the data area, and you'll get a count of unique client numbers
 
Top