Excel: How can I summarize by a field and then sort by it?

Question

I have customer support case data like this:

Customer   Time
Account_A  90
Account_A  45
Account_B  25
Account_B  90
Account_B  35
Account_C  100
Account_C  120

How would I summarize by customer name, doing a sum of time spent, and then sort the summary lines by time spent?

So my end result looks like this:

Customer   Time
Account_C  220
Account_B  150
Account_A  135

I used the subtotal function in Excel 2010, and then hid the details, but that only got me the summaries, I couldn't sort by the summaries, nor could I figure out how to copy just the summary lines into a fresh sheet and sort them there.

Answer 1

Here is a slightly better way. Assuming your columns are a and b, you can use this:

in the first cell of the c column place =IF(A1=A2,B1+B2,B1) in all the c columns cells except for the first one, =IF(A10=A11,C10+B11,B11)

This is really not ideal. It will generate the running totals for you, but will not break the data nicely.

EDIT: To break the data a little better, place =IF(NOT(A1=A2),"Account name: "&A1&" Total : "&C1,) in the D column. Paste special values to get the data somewhere else, then sort it to get rid of the zeros (so that they all go to the bottom, and you can just wipe them).

Data does need to be pre-sorted by account.

Answer 2

First create a pivot table from your data, and then use the Sort option to sort customer groups by time.

Answer 3

Here is a solution:

First, create a new sheet with two columns "Customer" and "Time". Add the individual customer names.

Second, on the original sheet, sort by "Customer". Use the sort button and sort by A-Z.

Third, for each customer, highlight the time cells all at once. So, in your example, for customer A, highlight the cells containing 90 and 45. This will display the sum of those cells in the bottom right hand corner.

Finally, add the sum of each cell to your new sheet.

Answer 4

I used the subtotal function in Excel 2010, and then hid the details, but that only got me the summaries, I couldn't sort by the summaries...

You should be able to sort by the summaries. Make sure you select "No header row" (Excel 97 at least) to then be able to sort by "Column B". I have just tried this in Excel 97, starting with your original data, and got this...

I can't imagine it's going to be any harder in Excel 2010?