I have listing of 1600 stores and sales by 4 week periods. I have a helper
cell to combine the state and city since this is one basically how I want the
date to break. But there may be say 15 unique stores in Atlanta, my pivot
shows the correct total sales for Atlanta but in the "count" I am getting
instead of 15 I expect it is counting the each store in each period (15
stores x 12 periods=180).

in the pivot I want the state, city total sales, unique stores (making up
the total) so that it would it would look like:
GA: Atlanta 5000 (sales) 15(unique stores)

It is counting the number of records that have a value for stores not a
unique count of the stores. Check out this link...

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

Jim Thomlinson


"Reno" wrote:

> I have listing of 1600 stores and sales by 4 week periods. I have a helper
> cell to combine the state and city since this is one basically how I want the
> date to break. But there may be say 15 unique stores in Atlanta, my pivot
> shows the correct total sales for Atlanta but in the "count" I am getting
> instead of 15 I expect it is counting the each store in each period (15
> stores x 12 periods=180).
>
> in the pivot I want the state, city total sales, unique stores (making up
> the total) so that it would it would look like:
> GA: Atlanta 5000 (sales) 15(unique stores)

Excel 2007 PivotTable
Count unique stores.
With and without formulas.
http://www.mediafire.com/file/rzy4vmjtwnk/01_11_10.xlsx

"Herbert Seidenberg" wrote:

> Excel 2007 PivotTable
> Count unique stores.
> With and without formulas.
> http://www.mediafire.com/file/rzy4vmjtwnk/01_11_10.xlsx
> .
> i beleive this will do it, but i don't see how to Insert Calculated Field, it does not appear on the Insert Ribbon, my ignorance is stunning.

Excel 2007 PivotTable
Count unique.
Added more pictures.
http://c0444202.cdn.cloudfiles.racks.../01_11_10.xlsx