The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. It can also ignore hidden rows, something that not many Excel functions can do.

This is the first in a series of posts that will look at how we can automate the SUBTOTAL function to summarise a dataset.

In this post we will start with a technique I thought of whilst having a walk. It enables you to summarise a dataset very easily using a single formula.

In the screenshot below there is a single formula in cell I3 that summarises the data in columns E and F. Note that rows 15 and 16 are hidden.

Columns A,B, and C, are for information only and come from the Microsoft help system for the SUBTOTAL function.

SUBTOTAL has two arguments. The first is a number that specifies which function to use. That requires a number from 1 to 11 or a number from 101 to 111. The second argument is the range to work with.

The formula in cell I3 is.

=SUBTOTAL(SEQUENCE(11),F4:F17)

The SEQUENCE function provides 11 numbers from 1 to 11 to use as the first argument in the SUBTOTAL. The calculations are based on the range F4:F17.

As you can see this one formula provides the results for 11 different calculations based on the range F4:F17.

Although the formula provides the results, you don’t know what the calculation means unless you have the label on the left hand side. The labels in cells H3:H13 are linked to the entries in column C.

Ignore Hidden Rows

We can tweak the formula a bit and provide the results that ignore the entries in hidden rows. Rows 15 and 16 are hidden and we can ignore those rows by making a small change to the formula.

The formula in cell J2 is.

=SUBTOTAL(SEQUENCE(11)+100,F4:F17)

This adds 100 to the SEQUENCE result and changes the SUBTOTAL results to ignore hidden rows. The numbers in column B ignore hidden rows. See the image below.

With dynamic arrays we can take this idea and convert it into a single formula that can provide the function labels as well as the results of the 11 SUBTOTAL functions.

In the next post we will use several dynamic array functions and techniques to create a two-column report range.