Overview of the SUBTOTAL Function

The SUBTOTAL function is a versatile tool used in Microsoft Excel and Google Sheets for performing various calculations on a dataset. It supports a range of operations from summing and averaging to finding minimum and maximum values. SUBTOTAL is particularly useful for calculating subtotals in filtered or hidden rows without counting certain lines.

Syntax and Parameters

The syntax for the SUBTOTAL function is as follows:

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

Where:

• function_number indicates which function to apply. For example, 9 for sum, 1 for average, 5 for maximum value.
• range1, range2, … are the data ranges to be calculated. Multiple ranges can be included.

Simple Implementation Example

For example, let”s calculate the subtotal of monthly sales for August using the SUBTOTAL function in a dataset that includes a business”s monthly sales.

 Dataset: A1: January B1: 300 A2: February B2: 450 A3: March B3: 500 A4: August B4: 600 Formula in Cell: =SUBTOTAL(9, B1:B4) 

This formula calculates the total of all values in column B (300+450+500+600 = 1850).

Complex Usage Scenarios

Working with Selected Data

Suppose that due to promotions, some months achieved sales that were atypical, and we want to exclude these months from the report. In this scenario, we can use the SUBTOTAL function”s ability to exclude hidden cells.

 If February and March are hidden: Formula: =SUBTOTAL(9, B1:B4) 

If February and March are hidden in Excel, the formula will only calculate the total of January and August (300 + 600 = 900).

Working with Different Function Numbers

Here is a table that includes different function numbers you can use with the SUBTOTAL function:

Select the appropriate function number to perform different calculations.

Daha fazla bilgi: https://support.microsoft.com/tr-tr/office/alttoplam-işlevi-7b027003-f060-4ade-9040-e478765b9939