Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Once you have used the subtotal function, you just need to highlight both the column and apply an auto Filter. You can then sort by time or customer. –  Barry Jun 6 '11 at 19:19

4 Answers 4

up vote 0 down vote accepted

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.

share|improve this answer
    
This'll work. Thx. –  Jordan Hudson Jun 6 '11 at 19:13

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

share|improve this answer

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.

sort button

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.

share|improve this answer
2  
That's terrible... It means a lot of manual work (the second part that is, not the first) –  soandos Jun 6 '11 at 18:52
    
I disagree. It is a quick solution. The author did not mention how large the sheet is or how many customer there are. Even, if there are a lot it would not take very long. Five minutes in fact. If you have a better answer, please submit it. –  slotishtype Jun 6 '11 at 18:56
    
I think my solution is better. It is still ungainly and ugly, but better. –  soandos Jun 6 '11 at 19:11
    
Ok, whatever works. –  slotishtype Jun 6 '11 at 19:23

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...

original data summarised and sorted by Total Time

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.