SUBTOTAL vs SUM – which do you use in Excel?
This week’s hint and tip is about looking at the SUBTOTAL function vs. the SUM function. Most of you will have heard of the SUM function and probably use it on a daily basis, but you might not have known that there is another function you can use that does the same thing but there is an extra element to it. The SUM function is on our Basic Excel training course but the SUBTOTAL function isn’t necessarily covered much so we decided to do a hint and tip on them both. We are going to go through it now below.
SUM Function
The SUM function has 1 argument to it and follows this layout =SUM(A1:A6). The ‘A1:A6’ part is the range of cells that you want to add up together.
SUBTOTAL Function
The SUBTOTAL function has 2 arguments to it and follows this layout =SUBTOTAL(9,A1:A6). The ‘9’ part is the function number, telling the subtotal function what it is doing (for example SUM or COUNT etc.). The number tells the SUBTOTAL function which you want it to use and when you type in ‘=SUBTOTAL(’ it comes up with a list of what the numbers stand for. The ‘A1:A6’ part is the same as above, the range of cells that you want to add up together.
When you type in the SUBTOTAL function in Excel it gives you a list of ‘function numbers’ to choose from. This list might seem confusing as it appears to have each function down twice but with two different numbers. The two different numbers are for the function including hidden values (numbers 1-11) and for the function ignoring hidden values (numbers 101-111).
The video below shows you the difference between SUM and SUBTOTAL and then within the SUBTOTAL function, the difference between using it including and ignoring hidden values.
Take a look below at the video to find out more and then try it out using the example spreadsheet below!
Click here to download the example spreadsheet shown in the video to try it out yourself!
We hope you have enjoyed this hint and tip, why not take a look at our previous one on creating a chart from a data subtotals sheet?