Türk | İngilizce |
---|---|
ALTTOPLAM | SUBTOTAL |
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:
Function Number | Description |
---|---|
1 | AVERAGE |
2 | COUNT |
3 | COUNTA |
9 | SUM |
5 | MAX |
6 | MIN |
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