I still can't get a normal pivot table to work because it seems like it
is counting the duplicate locations, for example item A it says 3 in
the pivot table although it is really only in 2 unique locations.

Thanks for the website link. I added the fomula
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,0,1) to column D and this
seems to correctly tell if it is unique. By including this column in
the pivot table, I am able to tell who many locations that item is in.
I am not sure I understand what that formula is doing....could you
explain some?

Thanks,
Andrew V. Romero


Jim Thomlinson wrote:
Give this a look...

http://www.contextures.com/xlPivot07.html#Unique
--
HTH...

Jim Thomlinson


" wrote:

I have a data table like

Item Location Qty
A 1 3
B 2 6
C 3 7
A 4 5
A 1 3
C 5 3

I am trying to count how many locations each item is in so I would want
it to say item A is in 2 locations (locations 1 and 4), item B is in 1
location, item C is in 2 locations, etc. I thought this would be easy
but can't seem to get it to work with pivot tables or formulas. So
whats the best way to do this???

-Andrew V. Romero