Help with sort and sum

[mwlemaster]

I need to have the attached worksheet sorted by ClientOS(col b) then by FinalStatus (Col c). After it is sorted, can a sum of each type of ClientOS by FinalStatus be computated? the file I have attached has been cut down significantly. I am dealing with approximately 25000 rows of data. Any help would be appreciated. Thanks in advance. Mike

[HansV]

Your data contain completely blank rows. It'd be best to remove those before continuing.

Yes, it's possible to sort by multiple columns:
First, click in a non-blank cell within the data. (If you haven't removed the blank rows, select the entire data range manually.)
In Excel 2003 or earlier, select Data | Sort..., and in Excel 2007 or later, click Sort & Filter | Custom Sort...
Specify that there is a header row, then specify ClientOS and FinalStatus as columns to sort on.
Finally, click OK.
The screenshot below is from Excel 2007.

x328.png

You mention that you want to sum something, but in the sample workbook there is no column that can be summed. Did you omit that column or do you in fact want to count something instead?

[mwlemaster]

Sorry, I meant count something... a tally of each type of machine (col b) by each type of status (col c)

[HansV]

There are basically two ways you can do this:

1) Subtotals

Sort the data as described in my previous reply.
Click in the table.
Select Data | Subtotals... (Excel 2003 or before) or activate the Data tab of the ribbon and click Subtotal (Excel 2007 or later).
If Excel complains that it can't find headers, click OK.
Select ClientOS as the column to change on, Count as function and FinalStatus as field to count, then click OK.

x329.png

Then invoke the Subtotals dialog again, and this time, select FinalStatus as the column to change on, and make sure to clear the check box "Replace current subtotals", then click OK.

x330.png

You'll get Outline buttons on the left hand side of the worksheet that let you determine how much detail you want to display.

2) Pivot table

For this option, you don't need to sort the data.
Click in the table.

Excel 2003: Select Data | PivotTable and PivotChart Report...
Click Next.
Click Next.
Click Layout...
Drag ClientOS and FinalStatus to the Row Area, and FinalStatus to the Data Area.
Click OK.
Specify where you want the pivot table (on a new sheet or on the active sheet).
Click OK.

Excel 2007: activate the Insert tab of the ribbon and click PivotTable.
Specify where you want the pivot table (on a new sheet or on the active sheet).
Click OK.
Drag ClientOS, then FinalStatus to the Row Items (or Row Labels) area.
Drag FinalStatus to the Data Items (or Values) area.

I have attached a zipped version of the workbook with both options.

Final.zip

[mwlemaster]

Works like a charm...Once again, Thanks!!! This is going to save LOADS of time. I will be singing Eileen's Lounge's praises!