Applying multiple Subtotals to your Excel table
Using Subtotals is much more flexible than I put in my previous post about Subtotals. I kept it compact because that is what I need to suit my needs in the work that I perform. Here I would like to expand - literally - the Subtotal function to show some other options that might help you do the work that you need to do.
I will start with a basic file; here it is before I do anything to it:
Now as before, go to the Data tab on the ribbon, and select the Subtotal Button:
Once you have that open, I will select At each change in Shipment Number, the Sum Function, and select subtotals for Discount, Net Amount, and Gross Amounts.
That way I will know the total cost of each shipment in my file. See the results below.
Now let's add some other functions to the list to show you what can be done. Again select the Subtotal button, now, to add some other functions don't forget to "Unclick" the check box beside Replace current subtotals. If you forget to uncheck this box, your new subtotals will show up and all your previous subtotals will disappear. So make sure you uncheck that box! While you are unchecking boxes, uncheck the previous Add subtotal to selections, in our case the Discount, Net Amount, and Gross Amount have all been unchecked.
Once that is unchecked you can add functions, I want to add count of Package Quantity.
Here are the results of that Subtotal:
I highlighted the first Subtotal results in green; the Count Subtotal I just completed in yellow. I now have the totals of the costs and the number of Packages in each shipment.
Now let's add a subtotal for another column without deleting our existing subtotals. We will repeat our steps for setting up a subtotal, I am adding in the average of the weight, destination code and discount. Here is what we get (the newest is highlighted in blue.)
As you can see all of my subtotals are here for easy access and use. By clicking the numbers in the top left I can reduce my file so that all I am looking at is my subtotals, I have highlighted all of my subtotals to make it easy to see what I have done.
There are other functions that you can use within the Subtotal Function such as: Max, Min, Product, Count Numbers, Standard Deviation, Standard Deviation for the Population, Variance, and Variance for the Population. I am not going into these in this blog, but this should give you some ideas to start experimenting with. Play around with some different data and functions to see what you come up with. That is after all the best way to learn new things, experiment with your data.
Thanks
Troy
See also
Table of contents
30 responses to "Applying multiple Subtotals to your Excel table"
Exactly what I needed. Thank you!!!
Very helpful, took care of my work need handily. THANKS,
Is there a way to check off all columns in one shot, other than each column one at a time. I have a YTD report that have 36 columns I have to check off one at a time when I do the subtotal and it would be great to just "check all", then take out about 5 columns, rather than check off 36. Any one know how to do that or if possible? Thank you.
Scott Eaton
Thank you so much! It was the tips I was looking for!!
Very helpful
Very helpful.
thanks.
Thanks a lot bro...
Very much helpful.
Thanks a lot..
Is there a way to condense the number of rows the subtotals takes up? So that it's not one row for the total, another for the average, etc.? I'm trying to condense my report so I don't have 'Total' and 'Average' after every change in value; I'd like the subtotals to go on the same line since one of the values I need an average for instead of a sum.
Hello Danielle,
I think this is not possible, at least I do not have a slightest idea on how this can be done.
Useful tip. Thanks
Is there a way to subtotal by shipment number and service in case you had different services within the same shipment? It doesn't appear that you can have multiple criteria in the "At Each Change" box?
For us to be able to assist you better, please send your sample workbook to alex@ablebits.com
good evening, can you please help me out to create a macro for the example below:
=69.69+43.51
=113.2+43.51
=156.71+43.51... it will be a great help.
Thanks, its very use full
ok...
helpful...
thanks...
how to highlight different subtotals with different colors when the data is large
sangeeta
Thank u ....it helps me a lot
Exactly what I needed. Thank you!!!
Thank you - this was valuable.
Thanks! This was super helpful!
Super.....it is very useful...
I have a inventory database which has got a different product and each product in different finish. As follows:
B8-01-10 EED301 SIL PCS 39
B8-01-20 EED301 SIL PCS 56
B8-01-30 EED301 SIL PCS 56
B8-02-10 EED301 SSS PCS 44
B8-02-20 EED301 SSS PCS 56
B8-02-30 EED301 SSS PCS 56
EED301 Total 307
Here EED301 is the product code and SIL & SSS are the finish. I want subtotal of this data finish wise. At selection level in subtotal, "At each change in" I selected "code" and "At subtotal to:" level I selected QTY and get this result. Even if I select finish along with Qty, no change. Please help how to do?
Hello, Tariq,
Suppose SIL / SSS is a separate column named Finish, then just specify Finish for "At each change in:", and QTY for "Add subtotal to:" in the Subtotal dialog window. So you'll get the summary for each group of the product.
If you want to get something different, then please clarify.
I am not getting proper result. I want code+finish wise total.
The output which I get:
Location Code Finish Unit Qty
B7-02-10 EED304 SIL PCS 12
B7-02-10 EED304 SSS PCS 26
B7-02-20 EED304 SSS PCS 56
B7-02-30 EED304 SIL PCS 40
B7-03-10 EED304 SIL PCS 57
B7-03-20 EED304 SIL PCS 56
B7-03-30 EED304 SIL PCS 40
EED304 Total 287
Thank you for the update, Tariq.
Please see how you can add multiple subtotals in this article.
Hope this is what you need.
Very useful
To calculate subtotal(1) i must add amount and what?
I have a question. I have created a spreadsheet to capture some measurements for 50 houses. Those measurements then have subtotals (for each house). I now want to add all the sub-totals into a total box. How can I do that when I think the maximum is 30? I hope you understand. I don't want to do =b1+b10+b20, etc, etc
Hello!
I recommend using this tutorial.