Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total then Sort
I am trying to define which customers has spent the most with us so far this
year in order. I have so far sorted all clients and invoice amounts for this year. There are over 3000 entries with each customer having between 5 and 20 invoices. The customers have been sorted into Alphabetical order in column A with the invoice amounts in column B. I need to add up the totals for each customer and then sort into cutomers with the highest expenditure. Please has anybody got any suggestions? Cheers Amanda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total then Sort
save your file first in case you screw up
1) sort by customer (just checking) 2) data subtotals at each change in customer use function SUM on column invoice amounts excel will go through and add subtotals per customer now look at the left of the screen. there are little outline levels, 1, 2, 3. click on level 2 so that you only see the totals per customer like this Customer1 Total $$$ Customer2 Total $$$ Now just select one of those cells with $$$ in it And then click on the Z-A (sort descending) button at the top now you have a list of the subtotals per customer sorted descending by amounts you can use data subtotals remove all to get rid of these if you don't like them one other thing to explore is pivot tables. have a look at this if you have spare time http://peltiertech.com/Excel/Pivots/pivottables.htm -- Allllen "Amanda" wrote: I am trying to define which customers has spent the most with us so far this year in order. I have so far sorted all clients and invoice amounts for this year. There are over 3000 entries with each customer having between 5 and 20 invoices. The customers have been sorted into Alphabetical order in column A with the invoice amounts in column B. I need to add up the totals for each customer and then sort into cutomers with the highest expenditure. Please has anybody got any suggestions? Cheers Amanda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total then Sort
It may be time to learn about pivottables. You can create these kinds of
summary tables pretty quickly. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Amanda wrote: I am trying to define which customers has spent the most with us so far this year in order. I have so far sorted all clients and invoice amounts for this year. There are over 3000 entries with each customer having between 5 and 20 invoices. The customers have been sorted into Alphabetical order in column A with the invoice amounts in column B. I need to add up the totals for each customer and then sort into cutomers with the highest expenditure. Please has anybody got any suggestions? Cheers Amanda -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort a Piviot table by the Total field in Excel 2002 | Charts and Charting in Excel | |||
Calculate total $ based on rows displayed by sort | Excel Worksheet Functions | |||
sort by month with a monthly total | Excel Discussion (Misc queries) | |||
Sort And Total | Excel Discussion (Misc queries) | |||
Sort creating total errors | Excel Discussion (Misc queries) |