Many apologies Andrew - mine was only half a solution. If you remove
sub-totalling from the pivot table (ie so you only have a Grand Total)
then the number of unique Locations will be given by = ROWS(A3:A50)
which is basically the cell address with the first location to the cell
address with the last location (from the pivot table) as the number of
rows will be representative of unique Item/Location combinations.

Sorry for not making that clear before.

Richard


RichardSchollar wrote:
Andrew

This should be really easy using a pivot table - make sure you have
Item and Location as Row fields, and then you could use either Qty (or
Item or location again) as a Data field and make sure you have the
summarisation set to Count (will be automatic if you use Item as it's
text).

Hope this helps!

Richard


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