The SUBTOTAL function in Google Sheets is a versatile tool designed to compute subtotals in a specified range of cells. This function is particularly useful for data analysis, allowing users to obtain aggregated values such as sums, averages, counts, and more, while also enabling options to ignore hidden rows.

Syntax

SUBTOTAL(function_code, range1, [range2, ...])

• function_code: A number between 1 and 11 or 101 and 111 that specifies the aggregation method. For instance, 1 is for AVERAGE, and 9 is for SUM.
• range1: The initial range of cells to perform the aggregation on.
• [range2, …]: Additional optional ranges to include in the subtotal calculation.

Example #1

SUBTOTAL(9, A1:A10)

This function calculates the sum of the values in cells A1 through A10. If the values in those cells are 10, 20, 30, 40, 50, 60, 70, 80, 90, and 100, the result will be 550.

Example #2

SUBTOTAL(1, B1:B10)

This function computes the average of the numbers in the range B1 to B10. If the numbers in B1 to B10 are 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10, the result will be 5.5.

Example #3

SUBTOTAL(3, C1:C10)

Here, the function returns the count of non-empty cells in the range C1 through C10. If there are 7 non-empty cells in that range, the result will be 7.

Error handling

• DIV/0!: This error appears if the function attempts to divide by zero, often due to an empty range.
• VALUE!: This indicates that the function received an inappropriate type of argument, like text when a number is expected.
• REF!: This error signals that a referenced cell range is invalid, often due to deleted cells.

Conclusion

In summary, the SUBTOTAL function is a powerful asset for anyone looking to perform aggregated calculations in Google Sheets. With its capability to handle a variety of functions and the option to ignore hidden rows, it stands out for data analysis tasks. Whether summing, averaging, or counting, utilizing SUBTOTAL enhances the efficiency and accuracy of your data management tasks.