Excel 2007 - Pivottable formula with COUNT

P

peter

I have created a pivot table with total dollar sales for several stores. I
have many columns, but one does show me COUNT on number of stores (e.g. 202)
and one shows dollar sales for the stores it covers (e.g. 1,567,580). I now
want to create a formula that takes the dollar sales and divides by store
count to provide dollars per store (1,567580/202 = $7,758.32/store)

I can do it mannually by adding a formula in cell after the table, but as I
expand and collapse the table, I may or may not have enough cells with
formula to cover each one.

I tried to create a formula within the pivot table ('Dollar Sales'
/COUNT('Dollar Sales')) but it seems to ignore the COUNT and provides the
exact same number just for 'Dollar Sales'.

Any suggestions? I am working in Excel 2007, so a bit different as to what
Pivottable does or does not do.

Thanks for all the help!
 
D

Debra Dalgleish

A calculated field will use the sum of the underlying values, even if a
different summary function is used in the pivot table layout.

You could add a field to the data source, e.g. StoreCount, and enter a 1
in each row. Use that field in the calculation, instead of the count of
stores.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top