Total then Sort

[Amanda]

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

 

[Allllen]

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

 

[Dave Peterson]

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/assistance/2002/articles/xlconPT101.aspx