Thanks for the help. The sumproduct is definately something I will
play around with more. The example I gave is part of a larger problem
that we are having in analyzing some data. Let me see if you would
agree with how I am going about this. We have a report that list if a
particular item has a stockout, but this report lists a stock out when
each particular location is empty as opposed to when the whole
department is out. For example

Item Loc Stockout (1=out, 0=not out)
A 1 1
B 2 1
C 3 0
D 4 1
B 5 0
A 3 1
D 6 1

So I would like to be able to generate data saying that we ran totally
out of A (both locations 1 and 3 ran out), and D but don't want to
include data like B since only one location ran out (2) but there was
another location that still has it (5). My thought was to first figure
out how many locations each item was stocked in (hence the original
post). Then sum the total number of stockouts per day and divide it by
the number of locations to get the number of total stockouts per day
per item. So item A had two stockouts, it was stocked in 2 places, so
2/2=1 total stockout. Obviously you would have to do some rounding to
account for issues like 5 stockouts but it is stocked in 6 locations,
thus we don't care because it wasn't totally out. Any ideas on a
better/quikier way to do this?

-Andrew V. Romero


RichardSchollar wrote:
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