SUBTOTAL function

The SUBTOTAL function in Excel is a powerful tool for performing various calculations on a range of data. It allows you to apply functions like SUM, AVERAGE, COUNT, and more to a subset of data, ignoring hidden or filtered rows. This can be particularly useful for creating summary reports or analyzing data without the need to manually adjust for filtered or hidden data.

Syntax

=SUBTOTAL(function_num, [ref1], [ref2], …)

Arguments

Here are the arguments you can use with the SUBTOTAL function:

function_num	The function number that specifies which operation you want to perform on the data. You can use a number from 1 to 11 for standard functions or 101 to 111 for functions that include hidden values.
[ref1], [ref2], …	(Optional) These are the references to the cell ranges that you want to perform the operation on. You can specify multiple references for the same function.

How to use

Let’s understand how to use the SUBTOTAL function with some examples:

Example 1: Using SUBTOTAL to calculate the sum of visible values in a range

This formula calculates the sum of visible values in the range A1:A10, ignoring any hidden or filtered rows. The number 9 represents the SUM function.

Example 2: Using SUBTOTAL to count visible values in a range

In this example, the formula counts the visible values in the ranges B1:B10 and C1:C10. The number 2 corresponds to the COUNT function.

Example 3: Combining SUBTOTAL with filtering

Suppose you have a table with data, and you apply a filter to it. You can use SUBTOTAL to perform calculations on the filtered data without needing to change the formula when you adjust the filter. For example, to calculate the average of visible values in a column:

Here, 101 represents the AVERAGE function with hidden values considered.

These examples demonstrate how the SUBTOTAL function can help you perform various calculations on a range of data while automatically considering hidden or filtered rows. It simplifies data analysis and reporting in Excel.