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.
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.
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.
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.
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.
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
How to display the calendar month of a date Formula...
Read MoreGetting started with IF statements Formula Friday #13 Formula Fridays...
Read MoreFinding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.
I can help bring your ideas to life. Let’s talk about what we can build together.
