Have you ever had the problem where you have used the =SUM function in your Microsoft Excel spreadsheet but then you hide or filter some of the data and the totals don’t change because they are still adding up the hidden values. This can cause a lot of confusion!
There is a better way!
So let’s say you want to add up the figures in the cell range B2 to B20 you would probably do it by creating this formula =SUM(B2:B20). The better way would be to use the formula =SUBTOTAL(109,B2:B20). So what does this mean?
The B2:B20 is obviously the range to be added up but what is the 109 about? The 109 is how you tell Excel to SUM visible cells ONLY in that range. So if you filter your data or hide some rows the total will automatically change to sum only what you can see, cool!
But wait there’s more! The full syntax of this formula is =SUBTOTAL(function_num,ref1,ref2,…). The function number tells Excel what you want to do with cells and ref1 is the first set of numbers to use, ref2 is the second set and so on to about 30 sets.
Some of the function numbers are:
101 – AVERAGE
102 – COUNT (counts cells with numbers only)
103 – COUNTA (counts numbers and text cells)
104 – MAX (find the biggest number)
105 – MIN (find the smallest number)
109 – SUM
There are others you will see when you give it a try. For example; if you just use 9 instead of 109 it will sum numbers seen and unseen just like the SUM function does, which defeats the purpose of using SUBTOTAL in my opinion so stick with the numbers from 101 onwards for a better result.
So let’s say you have a spreadsheet in which you want to allow for data being hidden at some point in the future. On that sheet you want to find the Average of a bunch of numbers that are stored in 3 different places on the sheet. I’ve made up 3 places in the example.
You would enter the formula =SUBTOTAL(101,B5:B50,E50:E100,J2:J25). The 3 different places (ranges) are shown and the 101 is because you want to find the Average.
When you are creating the formula, Excel will help you! Simply start typing =SUBT and you should see SUBTOTAL come to the top of the little list that pops up, press the TAB key on your keyboard and Excel will finish the typing for you, put in the first bracket and give you a list of all the different function numbers to choose from, pick the one you want and press TAB again, type in a comma and then use your mouse to drag over the cells you want to use, if you need multiple groups you can type commas between each set or hold down your CTRL key as you drag over the 2nd and subsequent ranges.
It takes a bit of practice to break the habit of using the standard functions like SUM and AVERAGE (I’m still working on that myself) but I hope you can see that it could be well worth the effort.
Give it a try and make your spreadsheets a little smarter!