Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table-using filters or groups
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table-using filters or groups
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). "Jeff" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table-using filters or groups
Use Pivot Table function "Top 10" instead of "Group"
This will download to Excel 2007 also: http://www.freefilehosting.net/download/3acdf |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table-using filters or groups
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). "bpeltzer" wrote: 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). "Jeff" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table-using filters or groups
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. -- Jeff "Herbert Seidenberg" wrote: Use Pivot Table function "Top 10" instead of "Group" This will download to Excel 2007 also: http://www.freefilehosting.net/download/3acdf |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table-using filters or groups
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table to contains more than 1 field groups | Excel Worksheet Functions | |||
Pivot table filters | Excel Discussion (Misc queries) | |||
Pivot Table Groups | Excel Worksheet Functions | |||
Pivot Table Date Groups | Excel Discussion (Misc queries) | |||
pivot table and groups | Excel Worksheet Functions |