Conditional Summing (2002sp1)

[kskrueger]

I have a logging table that contains the user name and run time information by date and time (note: many user names can appear multiple times). I would like to create a new table containing one row for each user with their run time accumulated. I could sort the table by user but how would I combine all the rows for a particular user into one row?

[HansV]

If you sort by user, you can have Excel insert subtotals: click somewhere in the table, then select Data/Subtotals...
Select User in the first dropdown list, and Sum in the second, and check Runtime in the third.
Click OK. you can hide/show details using the+/- buttons on the left hand side.

You can also create a pivot table; you don't need to sort for this. Click somewhere in the table, then select Data/Pivot Table Report...
Follow the steps of the wizard; place User in the row area, and Runtime in the value area. If necessary, double click it and select Sum.

If accumulated times can exceed 24 hours, make sure that they are formatted as [h]:mm instead of h:mm. This allows for times greater than 24 hours.

[kskrueger]

Thanks Hans! You've saved me tons of time and I've now created my first pivot table.