SUBTOTAL Function In Excel

This Tutorial demonstrates how to use the Excel SUBTOTAL Function in Excel to calculate summary statistics.

SUBTOTAL Main

SUBTOTAL Function Overview

The SUBTOTAL Function Calculates a summary statistic for a series of data. Available statistics include ,but are not limited to average, standard deviation, count, min, and max. See full list below in the function inputs section:

To use the SUBTOTAL Excel Worksheet Function, select a cell and type:

subtotal formula syntax

(Notice how the formula inputs appear)

SUBTOTAL Function Syntax and Inputs:

function_num – A number representing which operation to perform.

REF1 – Ranges or references containing data to calculate.

 

What is the SUBTOTAL function?

The SUBTOTAL is one of the unique functions within spreadsheets because it can tell the difference between hidden cells and non-hidden cells. This can prove to be quite helpful when dealing with filtered ranges or when you need to setup calculations based on different user selections. Since it also knows to ignore other SUBTOTAL functions from its calculations, we can also use it within large summarized data without fear of double-counting.

Basic Summary with SUBTOTAL

Let’s say that you had a table of sorted product sales, and wanted to create totals for each product, as well as create an overall total. You could use a PivotTable, or you can insert some formulas. Consider this layout:

Basic Subtotal Table

I’ve placed some SUBTOTAL functions in cells B5 and B8 that look like

Basic SubtotalFrom the syntax, you can use a variety of numbers for the first argument. In our specific case, we’re using 9 to indicate we want to do a sum.Basic Sub TTL Table

Let’s focus on cell B9. It has this formula, which includes the entire column B data range, but doesn’t include the other subtotals.

Basic Sub TTL

NOTE: If you don’t want to write all the summary formulas yourself, you can go to the Data ribbon and use the Outline – Subtotal wizard. It will automatically insert rows and place the formulas for you.

Difference in first arguments

In the first example, we used a 9 to indicate we wanted to do a sum. The difference between using 9 and 109 would be how we want the function to handle hidden rows. If you use the 1XX designations, the function will not include rows that have been manually hidden or filtered.

Here’s our table from before. We’ve shifted the functions over so we can see difference between the 9 and 109 arguments. With all visible, the results are the same.

9 Vs 109

If we apply a filter to filter out the value of 6 in col B, the two functions remain the same.

9 Vs 109 AutoHide

If we manually hide the rows, we see the difference. The 109 function was able to ignore the hidden row while the 9 function didn’t.

9 Vs 109-Manual

Change Math Operation with SUBTOTAL

You might like to sometimes be able to give your user the ability to change what type of calculations is performed. For instance, do they want to get the sum or the average. Since SUBTOTAL controls the math operation by an argument number, you can write this in a single formula. Here’s our setup:

Changing Operation Table

We’ve created a dropdown in D2 where the user can select either “Sum” or “Average”. The formula in E2 is:

Changing Operation new

Here, the IF function is going to determine which numerical argument to give to the SUBTOTAL. If A5 is “Average”, then it will output a 1 and SUBTOTAL will give the average of B2:B4. Or, if A5 equals “Sum”, then the IF outputs a 9, and we get a different result.

You could expand this capability by using a lookup table to list out even more types of operations you want to perform. Your lookup table might look like this

VLOOK Table

Then, you could change the formula in E2 to be

Subtotal VLOOK

Conditional formulas with SUBTOTAL

While SUBTOTAL has many operations it can do, it can’t check criteria on its own. However, we can use it in a helper column to perform this operation. When you have a column of data that you know will always have a piece of data in it, you can use SUBTOTALs ability to detect hidden rows.

Here’s the table we’ll work with in this example. Eventually, we’d like to be able to sum the values for “Apple”, but also let the user filter the Qty column.

table

First, create a helper column which will house the SUBTOTAL function. In C2, the formula is:

Remember that 103 means we want to do a COUNTA. I recommend using COUNTA because you can then have your reference cell of A2 be filled with either numbers or text. You’ll now have a table that looks like this:

Helper

This doesn’t appear helpful at first because all the values are just 1. However, if we hide row 3, that “1” in C3 will change to a 0 because it’s pointing at a hidden row. While it’s impossible to have an image showing the specific hidden cell’s value, you could check it by hiding the row and then writing a basic formula like this to check.

Now that we have a column that will change in value depending on if it’s hidden or not, we’re ready to write the final equation. Our SUMIFS will look like this

Conditional Check Table

In this formula, we’re only going to sum values from column B when column A equals “Apple”, and the value in column C is 1 (aka, the row isn’t hidden). Let’s say that our user wants to filter out the 600, because it seems abnormally high. We can see that our formula gives correct result.

Conditional Check

With this ability, you could apply a check to a COUNTIFS, SUMIFS, or even a SUMPRODUCT. You add in the ability to let your users control some table slicers, and you’re ready to create an awesome dashboard.

SUBTOTAL in Google Sheets

The SUBTOTAL Function works exactly the same in Google Sheets as in Excel:

SUBTOTAL Google

SUBTOTAL Examples in VBA

You can also use the SUBTOTAL function in VBA. Type:
application.worksheetfunction.subtotal(function_num,reh1)

Executing the following VBA statements

will produce the following results

Vba SUBTOTAL function

 

 


For the function arguments (function_num, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel