How do I sort subtotals for groups in Excel ?

[rtwoi]

I have a spreadsheet with lists of telephone calls and their cost.
I want to group calls to the same number together and get the total cost of
these calls. I then want to sort the groups into descending order of cost.

Both the subtotals facility and the Pivot table facility allow me to group
and sum the cost of the calls but I cannot then find a way to sort the
results in descending order of total cost. I am trying to find out what
called numbers have the highest total cost.

I am using Excel 2002.

 

[Dave Peterson]

First, I'm using xl2003. It may be different for xl2007.

With a pivottable:
rightclick on the phone number header in the pivottable.
Select field settings
Click the advanced button
Choose the sort option you want (descending)
Using field:
Sum of Cost
(or whatever you called it)

========
With subtotals:
Apply the subtotals
use the outlining symbols to the left to hide the details
(Keep the field totals showing)
Select this range
Data|sort
sort by cost in descending order.

The groups will be sorted--but the details won't be. If you want the details
sorted, then remove the subtotals, sort the way you want (phone number in
ascending, cost in descending), then reapply the data|subtotals.

Then hide the details and sort again.

 

[rtwoi]

Dave,

Thank you for your response. What you describe is how I expected it to
work. As I am using Excel 2002 without the latest patches however you get the
following message when you try to sort the subtotals.

"This removes the subtotals and sorts again. If you want to sort the
subtotalled groups, choose the Cancel button; then collapse the outline and
try again."

Trying again sends you round in a loop.

The Pivottable method does not seem to work at all in my version.

The solution is therefore to apply the latest Office updates to Office XP
(2002)

 

[Dave Peterson]

I get the same message when I had all the details showing and attempted to sort
the range.

You sure you had the details hidden?

 

[rtwoi]

Dave,
Yes the details are hidden. It also seems to depend on the quantity of data
as a small spreadsheet works OK but one over a page in size has the problem
has the problem.

As I have solved my problem I am not going to do any more investigation.

Thanks for your help.

 

[Pete_UK]

On a separate sheet you can extract a list of phone numbers dialled
and then using Advanced Filter you can reduce these to uniques so that
there is only one entry for each phone number. Then you can apply a
SUMIF formula to get the total cost for each number dialled, and then
just sort these in descending order. With a COUNTIF formula you can
obtain a count of the calls to each destination number, and then sort
these, so that you can obtain the Top-10 (or whatever) Most Frequently
called and the Top-10 Most Expensive destination.

Hope this helps.

Pete