Pivot Table-using filters or groups

[Jeff]

I am trying to create a sales report (not as I have been doing it - by hand).
Here's what I need: I need Sales by customer sorted decending - but I only
need the top 50 to show up. I know I could use the filter to do that,
HOWEVER, I need the Sales summary of the top 50, the summary of all the rest,
and then of course the grand total. The problem with using "filters" is that
you hide the "NOT" top 50 group. My hopes are that the "group" function
might be the answer... but I don't know enough. Put succinctly I need the
following:
1. Detail of top 50
2. Summary of top 50
3. Summary of all NOT top 50
4. Grand Total
Any help would be GREAAAAAATLY appreciated!
Oh, by the way, I just switched to Excel 2007

 

[bpeltzer]

If you use the filter, then the Autosum button, you'll get a function like
=subtotal(9,range), and the subtotal shown would be the summary of the top 50
(or whatever has passed the filter).
Two cells down you could enter =sum(range) which would give you the grand
total. And the summary of the others is just the difference between the sum
and the subtotal: =sum(range)-subtotal(9,range).

 

[Herbert Seidenberg]

Use Pivot Table function "Top 10" instead of "Group"
This will download to Excel 2007 also:
http://www.freefilehosting.net/download/3acdf

 

[Jeff]

Thanks for the response. I believe I understand what you said (maybe not),
but what I tried is not working. I am familiar with the =subtotal(9,range)
function, but I'm not sure how to use that in conjunction with a pivot
table???

Here's what I have: a pivot table with rows of customers, columns of dates
by month/qtr and data of the sum of invoices for the period. I use the "top
10" filter function to grab my "top 50"...

When I tried to use the subtotal and sum functions respectively they both
gave me the same result... I'm thinking that the "hidden" or filtered data is
simply not there when you are using a pivot table. (unlike when filtering a
worksheet, there are no hidden rows on the pivot table).

 

[Jeff]

Thanks Herbert... Can you explain why the =Sum(1/Countif(range, range)) -
range2 worked the way it did for you... I didn't seem to be able to replicate
it. (by the way, it was enclosed in { }, I don't know if that was
intentional or a product of the download.

By the way... I believe I got another solution to my problem too - at least
in Excel 2007 - you can "group" the 51st to end customers and hide the
detail, showing only the total for the group.

 

[Herbert Seidenberg]

The curly brackets {} mean that the formula
is an array formula and has to be entered
with SHIFT+CTRL+ENTER
I could have used
=SUMPRODUCT(1/COUNTIF(Cust,Cust))-Winners
which requires only ENTER