El Says:
“I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day.”
This is where the Subtotals feature is quite useful.
It can add a variety of footers to appear at breaks in the group. You get outlining too.
Select your range, then from the Data menu, choose Subtotals…
At each change in StartDate, Use function Sum, Add subtotal to Duration.
Rob,
More often than not, I need several aggregate functions on the subtotal line (e.g. Count for one col, Sum for another, Max on a third.) I usually sum on all three and use find-and-replace to change the first argument of the Subtotal function. If I need any data brought straight to the subtotal line I collapse the subtotals, goto visibles, key in a ref, then Ctrl+enter. Any alternate ideas?
Thanks,
Brett
The one thing that I would point out is to make sure the column you want to subtotal is sorted according to your needs. Otherwise, the results will not be what you want.
Mike: Good point
Brett: Your method is what I would choose if I needed subtotals on the same line.
If multiple subtotals is not an issue, try this:
After performing your first subtotals, select the range and open the Subtotals again, but this time ensure ‘Remove current subtotals’ is unticked.
Personally I don’t use Subtotals that often any more. I prefer to use pivot tables. The performance is much better on large files and the ability to display the data in a bunch of different ways is a real plus. Additionally the ability to create several pivot tables off of one set of source data is mighty handy. Where I still use subtotals is when one of the fields has more than 8,000 unique item. That causes pivot tables to die an untimely death…
Before I get a bunch of replies that subtotals provide a very different functionallity, I would just like to say “I know they do”. Just wanted to point out the pivot tables can be used in place of subtotals in a lot of areas with great results.
Here is bug / problem when using subtotals that I just bumped into
CAT PROD AMT
C1 P1 100
C1 P1 200
C1 P2 300
C2 P3 400
C2 P3 100
C2 P3 100
C3 P1 200
C3 P1 200
C3 P2 100
C3 P2 200
If you first subtotal on CAT and then on Prod (Replace Subtotals unchecked)
You will see that it works fine for CAT C1 and C3. However for C2 which has only 1 PRod viz. P3 the subtotal of P3 comes outside the CAT C2 Total….
Any one knows how to fix this
Sam: http://support.microsoft.com/kb/831824/ fixed it for me.