SUBTOTAL vs SUM function – which is the best to use in Excel?

SUBTOTAL vs SUM – which do you use in Excel?

SUM function: Excel iconThis 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.


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.



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?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy