Formula Friday #17
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
I don’t know about you, but I often forget there are other ways to add totals and/or subtotals to my data because I’m so used to using one of the SUM Functions. But there is actually a Function that can take care of most if not all of your totaling needs.
The SUBTOTAL Function
Some of you might be familiar with it, others might not be as familiar.
It’s, in essence, a all in one subtotaling tool. Although it is called SUBTOTAL it actually does do a variety of calculations such as Average, Count and Min/Max.
One thing you might not know, even if you have used the Function before. Is the difference between each of the function options it allows.
There are 11 different function options within SUBTOTAL. Those options are first numbered 1 – 11. As you can see from the above picture. And those who have used it before will know 9 is SUM. Those same 11 options are listed for a second time, in the same ordered, and numbered 101 – 111.
The distinction between the two sets is whether or not the end result includes or excludes hidden values.
What do I mean by hidden values?
Hidden values in this instance means any row you have right-clicked on and selected hide.
The image above is for a column but it will have the same option is used on a row.
Once this row is hidden from view, depending on whether your SUBTOTAL formula uses the first set of function options or the second set the value will be different.
In the image above, the data set on the left uses the SUM function option which includes hidden values
=SUBTOTAL(9,E3:E19)
And the data set on the right uses the SUM function option which excludes hidden values
=SUBTOTAL(109,E3:E19)
If values are hidden by using filters. Those values will be excluded from the calculation regardless of which function option number you use.
Alternative ways to add a SUBTOTAL to your data
Converting your data into a Table is a great way to open up additional functionality to your data set.
And has the added benefit of being able to add in subtotals at the click of a button. By selecting the Total Row option in the Design Ribbon for Table Tools.
The default setting when adding a subtotal to your Table is SUM. This can be changed to one of the other calculations by selecting the relevant one from the drop down.
When adding a subtotal to your Table, it will work in the same way as the calculations coded 101 – 111 from the SUBTOTAL Function.
In other words, whenever a row is hidden, with or without using filters, the subtotal will display the value for only the visible cells.
Using the Subtotal tool in the data ribbon
Is another way to add subtotals to your data.
Using this tool will allow you to sort and group your data and add in subtotals based on a change in one of your sort options.
In this example they have been added in for each change in Month.
Unlike the subtotals used for tables, these subtotals will only exclude values that are hidden using filters.
Which means when you close a group. Although the values are hidden from view, they will still be included in the groups subtotal and the grand total.
Final thoughts
Adding subtotals to your data is simple once you know what you want the subtotal to do, and what data you want to be included in it. Luckily SUBTOTAL Functions ignore other SUBTOTAL Functions, so you won’t run the risk of double counting.
If you have a data set that would benefit from some subtotaling magic, but need some extra support, feel free to drop me a line.
Until next time,
Ax
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
