SUBTOTAL

The SUBTOTAL function returns a subtotal for a range of cells.

SUBTOTAL(function-num, range, range…)

function-num: The function to use for the subtotal.

AVERAGE (1): Uses the AVERAGE function.

COUNT (2): Uses the COUNT function.

COUNTA (3): Uses the COUNTA function.

MAX (4): Uses the MAX function.

MIN (5): Uses the MIN function.

PRODUCT (6): Uses the PRODUCT function.

STDEV (7): Uses the STDEV function.

STDEVP (8): Uses the STDEVP function.

SUM (9): Uses the SUM function.

VAR (10): Uses the VAR function.

VARP (11): Uses the VARP function.

AVERAGE_H (101): Uses the AVERAGE function and skips hidden rows.

COUNT_H (102): Uses the COUNT function and skips hidden rows.

COUNTA_H (103): Uses the COUNTA function and skips hidden rows.

MAX_H (104): Uses the MAX function and skips hidden rows.

MIN_H (105): Uses the MIN function and skips hidden rows.

PRODUCT_H (106): Uses the PRODUCT function and skips hidden rows.

STDEV_H (107): Uses the STDEV function and skips hidden rows.

STDEVP_H (108): Uses the STDEVP function and skips hidden rows.

SUM_H (109): Uses the SUM function and skips hidden rows.

VAR_H (110): Uses the VAR function and skips hidden rows.

VARP_H (111): Uses the VARP function and skips hidden rows.

range: The range of cells for which to calculate a subtotal.

range…: Optionally include one or more additional ranges of cells for which to calculate a subtotal.

Notes

  • You can skip hidden rows by adding 10 if function-num is a single digit and 1 if function-num is two-digits. For example, use 101 for AVERAGE.

  • Hidden columns never affect the value of SUBTOTAL.

  • If range or range… includes other SUBTOTAL formulas, they are ignored.

Examples

Given the following table, in which row 2 is a hidden row:

A

1

63

2 (hidden)

12

3

42

4

17

5

52

=SUBTOTAL(1, A1:A5) returns 37.2, the average of the subtotal of cells A1:A5 (including hidden rows).

=SUBTOTAL(105, A1:A5) returns 17, the minimum value in cells A1:A5 when hidden rows are skipped.

=SUBTOTAL(9, A1:A5) returns 186, the sum of cells A1:A5 (including hidden rows).

=SUBTOTAL(109, A1:A5) returns 174, the sum of cells A1:A5 when hidden rows are skipped.

See alsoVARPVARSUMSTDEVPRODUCTAVERAGE
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Uporabno?
Omejitev znakov: 250
Zgornja omejitev znakov je 250.
Hvala za povratne informacije.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.