How to Use the SUBTOTAL Function in Excel

Today, we’ll delve into the SUBTOTAL function, available in both Microsoft Excel and Google Sheets. This versatile function performs a variety of operations on a set of cells, including sum, average, count, max, min, and more. A key feature of SUBTOTAL is its ability to disregard other SUBTOTAL calculations within the same range.

Basics of SUBTOTAL Function

The syntax for the SUBTOTAL function is consistent across both Excel and Google Sheets:

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

• function_num: Specifies the operation to execute. The values range from 1-11 in Excel and 1-9 in Google Sheets.
• ref1, ref2, etc.: Refers to one or more cell ranges to which the subtotal operation will be applied.

Examples of Using SUBTOTAL Function

Here are some practical applications of the SUBTOTAL function:

In these examples, the function_num determines the specific operation to be performed on the data range. For example, 9 and 109 are used for summing, 1 and 101 for averaging, and 2 and 102 for counting. Note the variation in function codes between Excel and Google Sheets.

Benefits of SUBTOTAL Function

The SUBTOTAL function proves particularly valuable when working with filtered data, as it computes results based solely on visible cells. This is especially useful in ignoring any rows hidden due to filtering, offering more accurate outcomes in such cases.

This guide should provide a comprehensive understanding of the SUBTOTAL function’s capabilities and demonstrate its effectiveness in processing calculations in Excel and Google Sheets.

More information: https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939