Microsoft Excel Functions: SUBTOTAL

The SUBTOTAL function returns the subtotal from a list or database in Excel. SUBTOTAL will give you instant answers for your filtered data

Download the accompanying file(s) for this article.


The syntax for SUBTOTAL is different to other Excel functions that you may have come across. A function number is needed to tell Excel which calculation you want to perform with the SUBTOTAL function

=SUBTOTAL(function_num,ref1, ref2)

The function number is required and can be chosen from 1-11 or 101-111. 1-11 will include manually hidden rows whereas 101-111 will exclude manually hidden rows from the calculation.

The below table from the Microsoft website details what each function does:


Source: Microsoft Website


SUBTOTAL in Action

In the accompanying workbook to this article, you will find 1,000 rows of sales data for a full year. The list includes customers and products. Scroll to the bottom of the data (ctrl+shift+down arrow) where there are two rows totaling the data, one using SUBTOTAL and one using SUM.

Currently, the figures are the same, this is because you haven’t applied any filters to the data. The formula for the SUBTOTAL row is:


Function 9 is ‘SUM’ and D3:D1002 is the range to SUM

Click on the drop-down arrow on the ‘Customer’ field and select Alan Greene (you may need to tick ‘select all’ first to un-select all the records).


Scroll to the bottom of the file and observe how the SUBTOTAL formula has only totaled the displayed rows. The SUM function has remained the same and doesn’t really provide us with any meaningful analysis of the data.


Why is This Useful?

Using subtotal can help speed up how you handle and analyse your data. Using SUM for a calculation such as this will not display to correct sales totals for Alan Greene and cause you to spend more time calculating what the sales are for the displayed data.

Ideal Solution?

With your data nicely laid out in a tabular format the best way to analyse it would be with a pivot table. An article series on pivot tables will follow soon.

You could also use SUMIF or SUMIFS to create data tables, however, a pivot table is much more flexible.


If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Analysis


Leave a comment

Your e-mail address will not be published. Required fields are marked *