Skip to content

Behold the SUBTOTAL Function in Excel

In this article, you will learn all about the SUBTOTAL function.

The SUBTOTAL function is great because it can perform many different calculations, and you can configure it to ignore or include hidden rows.

Let’s take a look at how this function works.

The SUBTOTAL Function

The SUBTOTAL function can perform several distinct calculations, which include:

  • Calculating the average
  • Counting numbers
  • Counting non-empty cells
  • Calculating the maximum
  • Calculating the sum
  • And many more

The SUBTOTAL function has two required arguments, but it can accept up to 255. The arguments are:

  • Function_num – This specifies what type of calculation the function should perform (Required)
  • Ref1 – This is a reference to the range of cells the function should consider for its calculation (Required)
  • Ref2 – This can be another reference the function can perform calculations on (Optional)
  • Ref3, Ref4, etc.

When you use the SUBTOTAL function, you set the first argument to be a number that indicates what type of calculation the function should perform.

The numbers and the calculations the different numbers perform can be found here.

The SUBTOTAL Function in Action

Here is an example of the SUBTOTAL function in action.

In the example above, the first argument in the SUBTOTAL function indicates that it should perform a sum of the numbers.

The second argument is the range of cells containing the numbers, and the SUBTOTAL function adds all the numbers to calculate a result of 100.

Ignore Hidden Rows

A nice feature of the SUBTOTAL function is that you can configure it to ignore the numbers in hidden rows.

You do this by selecting the proper number for the first argument. Take a look at this example.

Notice that the first argument is set to 109. This number signifies that the SUBTOTAL function should sum the numbers in the referenced range but also ignore any numbers in hidden cells.

If we hide rows 34, and 5, the SUBTOTAL function updates and only sums the visible cells.

How cool is that!?

Also, if you missed it before, click here to see the list of possible numbers you can use for the first argument of the SUBTOTAL function. This resource will tell you which numbers to use for which calculation.

SUBTOTAL Always Ignores Filtered Cells

Let’s take a look at one last example.

Here, the SUBTOTAL function is set up to sum the numbers one more time.

Also, if you look closely at the first argument, it is set to 9, which is supposed to include hidden rows in the calculation.

The big difference this time is that a filter has been applied to the list of numbers.

If we use the filter to hide some of the cells, the SUBTOTAL function will not consider the cells that have been filtered out.

Therefore, it’s always important to remember that the SUBTOTAL function will always ignore cells that have been hidden by a filter.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top